SQL server query to output the data shown below

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):

    [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');

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

enter image description here

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.

Leave a Comment