SQL Substring

[Bondorofi]

SQL(2005) Server Management Studio

Can someone please help me. I have records denoted by 2 characters + 3 numbers (bb-100) and 5 characters + 3 numbers (hsaid-101)

Now I want to extract the numbers part only and use it in my database as a sequence...100, 101, 102 etc no characters.

I did the following...

CHARINDEX(Record_Number, '-') AS MEPI_ID, SUBSTRING(ddddd.Record_Number, 4, 3) AS MEPI_Seq,

This only works on records with 2 characters + 3 numbers (bb-100) yet I want a function that can handle both situations. Any suggestions?

Thanks

[chazzy]

Maybe like a string split function? how about something like this

http://searchvb.techtarget.com/tip/0,289483,sid8_gci932171,00.html

[russell]

if the rightmost 3 are always numeric, just use Right() function
SELECT Right(fieldName, 3)
and to make it numeric datatype
SELECT Cast(Right(fieldName, 3) as int)

[Bondorofi]

Thanks Russell! Perfect solution