DECLARE @tab TABLE (id INT, val INT)

INSERT INTO @tab
SELECT 1, 95 UNION ALL
SELECT 2, 959 UNION ALL
SELECT 3, 44 UNION ALL
SELECT 4, 959898 

SELECT TOP 1 id, val
FROM @tab
WHERE PATINDEX('%'+CAST(val AS VARCHAR(20))+'%','9598989898954412') = 1
ORDER BY val DESC


-- Alternative code (the above is a little oversized for this purpose...) -> driven by Atif's solution below...
SELECT TOP 1 id, val
FROM @tab
WHERE '9598989898954412' like CAST(val AS VARCHAR(20))+'%'
ORDER BY val DESC

-- result set:
-- ID  val
-- 4   959898

Declare @t1 Table(mid int, val varchar(100))
Insert into @t1
 
Select 1,'123' union all
    
Select 2,'123456' union all
 
Select 3,'123456789' union all
  
Select 4,'123456789123'
Declare @vChk varchar(100)
Set @vChk = '123'
Select Top 1 *  from @t1
where  val like '%' + @vChk + '%'
Order By Len(val) desc

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

DECLARE @tab TABLE (id INT, val INT)

INSERT INTO @tab
SELECT 1, 95 UNION ALL
SELECT 2, 959 UNION ALL
SELECT 3, 44 UNION ALL
SELECT 4, 959898 

SELECT TOP 1 id, val
FROM @tab
WHERE PATINDEX('%'+CAST(val AS VARCHAR(20))+'%','9598989898954412') = 1
ORDER BY val DESC


-- Alternative code (the above is a little oversized for this purpose...) -> driven by Atif's solution below...
SELECT TOP 1 id, val
FROM @tab
WHERE '9598989898954412' like CAST(val AS VARCHAR(20))+'%'
ORDER BY val DESC

-- result set:
-- ID  val
-- 4   959898

DECLARE @t TABLE
(id INT, num INT)

INSERT into @t
SELECT 1, 95 UNION ALL
SELECT 2, 959 UNION ALL
SELECT 3, 44  UNION ALL
SELECT 4, 959898 UNION ALL
SELECT 5, 98954412
 
DROP TABLE #z

SELECT id, num, LEN(num) matchlen, CHARINDEX(cast(num as varchar(20)),'9598989898954412',1) charpos
INTO #z
FROM @t

SELECT id, num FROM #z 
WHERE matchlen = (SELECT MAX(matchlen) FROM #z)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here