Function to Retrieve only Integer part from the given string in SQL Server

Amatya
Posted by Amatya under Sql Server category on | Points: 40 | Views : 244
Alter FUNCTION RetrieveInt(@Val VARCHAR(200))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNum VARCHAR(1000)
SET @Count = 0
SET @IntNum = ''
WHILE @Count <= LEN(@Val)
BEGIN
IF SUBSTRING(@Val,@Count,1) >= '0'
AND SUBSTRING(@Val,@Count,1) <= '9'
BEGIN
SET @IntNum = @IntNum + SUBSTRING(@Val,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNum
END
GO




You can call this function like -
SELECT dbo.RetrieveInt('Amatya200Adi978tya');

The Output will be -
200978



Thanks
Amatya

Comments or Responses

Login to post response