Skip to content
Advertisement

Extracting Numeric values from a column (VARCHAR) based on a preqrequisite

I currently access a table view from SQL server and write custom SQL query to retrieve/filter/create custom column as per my need before exporting. Now in that view one of the columns have text value as following

Description
Transaction 12345678-1
Tx234567890-2
45678901-12
55667788-20    
Inv# 12457800-2

Now all I want is to create a custom column which would extract all the numeric values and – from that string as following.

Description
12345678-1
234567890-2
45678901-12
55667788-20
12457800-2

If you can please help would be greatly appreciated.

Raw Data and desired output – https://drive.google.com/file/d/1ze-S15kWi8qcLro8xx9vpSgnPIYCXLSa/view?usp=sharing

Thanking you in advance.

Advertisement

Answer

The pattern I am trying to identify and extract from a string is as follows

DDDDDDDD-D or DDDDDDDD-DD

D stands for numeric digit

So the following worked for me

SELECT description, 
CASE 
    WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%', description)>0 THEN SUBSTRING(description, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%', description), 11)
    WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%', description)>0 THEN SUBSTRING(description, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%', description), 10)
END AS refno, 
*
FROM
XXXXX
where (description like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]%' OR description like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]%')
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement