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