Few days back, my friends had asked a question “I want to get only integer part in string. for example, string contain ‘asb645pqr231means’ i want only integer part like 64523. how can i get?“. I have mentioned two method to do it.
Method 1:
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR (8000)
SELECT @str = ‘asb645pqr231’
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE (@str, CHAR (65 + @loop), ”)
SET @loop = @loop + 1
END
SELECT @str
Method 2:
SET NOCOUNT ON
DECLARE @KeepValues VARCHAR (50)
DECLARE @Str VARCHAR (500)
SET @Str= ‘asb645pqr231’
SET @KeepValues = ‘%[^a-z]%’ — if you want only characters in the output
SET @KeepValues = ‘%[^0-9]%’ — If you want only integers in the output
WHILE PATINDEX (@KeepValues, @Str) > 0
SET @Str = STUFF (@Str, PATINDEX(@KeepValues, @Str), 1, ”)
select @Str
There may be more ways do it.
