Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 extracting the integer part of the parameter

Author  Topic 

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-14 : 15:10:39
Hi All,

I need to extract the integer part of the string that exists on the right side of the string
so for e.g.
I have BASIC56 then I only want 56 and if I have BASIC6 then I only want 6 and BASIC100 then I want 100 to be extracted out in sql.

Is it possible to do that?

any help will be appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-14 : 15:45:05
I got this from here:
http://stackoverflow.com/questions/14700214/how-to-extract-numbers-from-a-string-using-tsql
DECLARE @String varchar(255) = 'This1 Is2 my3 Test4 For Number5 Extr@ct10n';


SELECT
CAST((
SELECT CASE --// skips alpha. make sure comparison is done on upper case
WHEN ( ASCII(UPPER(SUBSTRING(@String, Number, 1))) BETWEEN 48 AND 57 )
THEN SUBSTRING(@String, Number, 1)
ELSE ''END
FROM
(
SELECT TOP 255 --// east way to get a list of numbers
--// change value as needed.
ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) AS Number
FROM master.sys.all_columns a
CROSS JOIN master.sys.all_columns b
) AS n
WHERE Number <= LEN(@String)
--// use xml path to pivot the results to a row
FOR XML PATH('') ) AS varchar(255)) AS Result

Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-14 : 15:53:18
Thank you.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-14 : 16:57:50
That stack overflow thing may be overkill if you just have a single string per table row. Perhaps this:

;with yourTable (id, myString) as
(
select 1, 'basic56' union all
select 2, 'BASIC6' union all
select 3, 'baseid100' union all
select 4, 'a;ljasd200010' union all
select 5, '123dks'
)

select id
,case
when patindex('%[^0-9]%', reverse(myString))-1 < 1 then NULL
else right(myString, patindex('%[^0-9]%', reverse(myString))-1)
end digits
from yourTable

OUTPUT:
id digits
----------- -------------
1 56
2 6
3 100
4 200010
5 NULL


Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -