CODEWITHSUNDEEP
sql-servert-sql
ebooyens
ebooyens

Reputation: 628

Extract number from part of a string

I'm trying to figure out how to extract a number from a string (nvarchar I guess I should say) with one complication, I only need the number from the right side of the string. That probably doesn't make sense so here's an example, if the string is 12857 ABC 456 then I only want the 456 part.

Up and till now these strings have just been in this format ABC 456 but now they've started prefixing with numbers which is causing my existing code to fail. This is what I'm using at the moment which will return 12857456 in the first example:

CONVERT (int, LEFT (SUBSTRING([FIELD], PATINDEX('%[0-9.-]%', [FIELD]), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING([FIELD], PATINDEX('%[0-9.-]%',[FIELD]), 8000) + 'X') - 1))

Thank you!

Upvotes: 0

Views: 159

Answers (3)

Luke Franklin
Luke Franklin

Reputation: 355

i have added in 2 reverse functions one before and one after the first LEFT function.

the REVERSE function simply reads the text right to left instead of the SQL default of left to right, so THE String "12857 ABC 456" would read "654 CBA 75821".

your query that you wrote could then handle reading from the left as you would get the last part 654 C.

i then had to change the last part of the query that read -1 to -2 to then get the value 654

then the REVERSE function before the LEFT function would then change the number 654 back to its default of 456.

   CONVERT(int,REVERSE(LEFT(reverse(SUBSTRING([Name], PATINDEX('%[0-9.-]%', [Name]), 8000)), PATINDEX('%[^0-9.-]%', SUBSTRING([Name], PATINDEX('%[0-9.-]%',[Name]), 8000) + 'X') - 2)))

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

this should work:

SELECT CONVERT(INT, REVERSE(SUBSTRING(REVERSE([field]),1,CHARINDEX(' ',REVERSE(@field))-1)))

Upvotes: 1

jean
jean

Reputation: 4350

If your values got fixed size use RIGHT string function. If the new number prefix is fixed size use LEFT to get rid of it and use your old code to do the rest.

If no string or number sequence is fixed size use PATINDEX to get rid of the new prefixed number and apply your old code.

Upvotes: 1

Related Questions