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

Question

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.

Answer 1

Try this:

declare @t table (name varchar(50))

insert into @t values ('Tx234567890-2'),('Inv# 12457800-2')

select substring(name,PATINDEX('%[0-9]%',name),len(name)) from @t

Answer 2

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]%')