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