How to get the numeric value from this code

Question

HI this is my sample code , where i need to extract only the numeric values from the given string,how do i extract

sample input
XS8011
XS8011
XS8011
X2304_2000
X2314_2001

after under score value i need the numeric value
required output
2000
2001

i tried this query but it is giving all the results,can some one pls correct this query

select Customer,SUBSTRING(Customer,CHARINDEX('_',Customer)+1,LEN(Customer)) from tbluser
this didnt work

Answer 1

Hi @Naresh y

I tried this query but it is giving all the results

Syntax: CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
If CHARINDEX does not find expressionToFind within expressionToSearch, CHARINDEX returns 0.
That's why this code SUBSTRING(Customer,CHARINDEX('_',Customer)+1,LEN(Customer)) returns entire Customer when there is no '_' in the string.
What you need is a WHERE clause to filter out records that do not have _, just as Tom answered.

Additional Notes:
If you are using SQL Sever 2017+ , you could try TRANSLATE function to remove non-numeric characters like this:

SELECT REPLACE(TRANSLATE( Customer,  
                         'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',  
						 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'),--keep 'AAAAA...' same length as the second argument  
			   'A', '')  
from #tbluser  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

select Customer,SUBSTRING(Customer,CHARINDEX('_',Customer)+1,LEN(Customer))  
from tbluser  
where CHARINDEX('_',Customer) > 0  
  

Tom

Answer 3

Try:

SELECT   
	TRY_CAST(CASE WHEN CHARINDEX('_',Customer) > 0 THEN RIGHT(Customer,CHARINDEX('_',REVERSE(Customer))-1) ELSE NULL END as INT) as Num  
FROM @tbluser