CODEWITHSUNDEEP
sql-server
Shivkumar kondi
Shivkumar kondi

Reputation: 6762

Best alternatives for extracting last integer data from given string from SQL Server

I am trying to extract only the integer data part at the end of this string.

Example: `/coronavirus/07-2020covid19/58241'

My code:

select  
    reverse(substring(reverse('/coronavirus/07-2020covid19/58241'),
                      1 ,
                      patindex('%/%', reverse('/coronavirus/07-2020covid19/58241')) - 1))

Output: 58241

Execution stats for above statement:

enter image description here

I just want to know the best way of doing this as I have around 25.5M rows. So I am basically looking for optimal fast solution with lesser load on SQL Server.

Upvotes: 0

Views: 379

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

One method is to split the string:

select s.value
from string_split('/coronavirus/07-2020covid19/58241', '/') s
where '/coronavirus/07-2020covid19/58241' like '%/' + s.value;

If you also want to throw in that it is a number, you can add:

s.value not like '%[^0-9]%'

If this is a column in a table, you would use apply:

select t.*, s.value
from t cross apply
     split_part(t.col) s
where t.col like '%/' + s.value;

Another method looks for the last '/'. I would use stuff():

select stuff(str, 1, len(str) - charindex('/', reverse(str)) + 1, '')

Upvotes: 2

Related Questions