SQL server query to output the data shown below

Question

I have column which contain strings like:

1. "HDPE Unlaminated Fabric-60gsm 101 White"
2. "SUP 150 110 GSM white Cut piece"
3. "HDPE LF 140/120 GSM Mix color"
4. "self adhsv(50gsm) HDPE

Could you help me writing query to get the output as shown below:

1. "HDPE Unlaminated Fabric-60gsm 101 White" | 60gsm
2. "SUP 150 110 GSM white Cut piece"         | 110 GSM
3. "HDPE LF 140/120 GSM Mix color"           | 140/120 GSM
4. "self adhsv(50gsm) HDPE                   | 50gsm

Answer 1

You can create a statement using T-SQL built-in functions that is returning your desire result but it:

• will be complex
• will become even more complex when your conditions are changed

So, it will be difficult for you to change it and support it. For me, such tasks can be handled more appropriately using regular expressions.

We have two options:

• read the data and perform the transformation using regex in the client side or in your back end routines (JavaScript, C#, etc);
• to create SQL CLR functions that allows to use regex function in the context of T-SQL statement

For the second option follow the example I have linked to create a RegexMatches function and you will be able to do the following (like in my environment):

DECLARE @DataSource TABLE
(
    [value] NVARCHAR(128)
);

INSERT INTO @DataSource ([value])
VALUES ('HDPE Unlaminated Fabric-60gsm 101 White')
      ,('SUP 150 110 GSM white Cut piece')
      ,('HDPE LF 140/120 GSM Mix color')
      ,('self adhsv(50gsm) HDPE');

SELECT *
FROM @DataSource 
CROSS APPLY dbo.fn_Utils_RegexMatches ([value], '(?i)\d+[\d/]+\s*gsm');

Few important notes:

• you will need to put some effort to understand and implement SQL CLR functions
• for the past years Microsoft have added a lot of built-in functions like string aggregate and string split without order, but as SQL Server 2019 there is no built-in support for regex
• if you have no time to learn or you are not allowed to support/create SQL CLR functions it will be better to perform the operation outside the SQL Server

If you implement regex SQL CLR functions you can find solving particular issues very simple and clear.