String Function

Hello All,
We have a table which has data like below for a column ID
TEST300
TEST301
TEST302
TEST303
TEST304
How to extract data after 'test' like just 300,301,302,303,304.
Please help.
Star79Asked:
Who is Participating?
 
Vikas GargConnect With a Mentor Business Intelligence DeveloperCommented:
Hi,

Can try these ways
DECLARE @TS2 VARCHAR(50) = 'TEST'
SELECT SUBSTRING(Column,LEN(@TS2)+1,3),RIGHT(Column,3)

Open in new window

0
 
Daniel WilsonConnect With a Mentor Commented:
Borrowing a bit of code from here ... http://www.experts-exchange.com/Database/Miscellaneous/A_17544-ORDER-BY-a-combined-value-text-numercial.html

I think this code snippet should do:
select cast(substring(ID, patindex('%[0-9]%', ID) , len(ID)) as int) as NumberPortion from MyTable

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Substring(ID,5,999)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.