SQL server query to output the data shown below

by

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

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.

More Related Contents:

  1. T-SQL SEQUENCE runs out, no cycle, what happens?
  2. How do I create a unique constraint that also allows nulls?
  3. How do I get list of all tables in a database using TSQL?
  4. Get first day of week in SQL Server
  5. What is the significance of 1/1/1753 in SQL Server?
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. Escape a string in SQL Server so that it is safe to use in LIKE expression
  8. Using a if condition in an insert SQL Server
  9. Insert Update trigger how to determine if insert or update
  10. SQL time difference between two dates result in hh:mm:ss
  11. What is the best way to truncate a date in SQL Server?
  12. Dynamic SQL (passing table name as parameter)
  13. SQL Server: Difference between PARTITION BY and GROUP BY
  14. How do you truncate all tables in a database using TSQL?
  15. Understanding SQL Server LOCKS on SELECT queries
  16. SQL Server: How to select all days in a date range even if no data exists for some days
  17. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  18. TSQL: How to convert local time to UTC? (SQL Server 2008)
  19. T-SQL Dynamic SQL and Temp Tables
  20. Find non-ASCII characters in varchar columns using SQL Server
  21. How can I determine in SQL Server if a dateTime range overlaps another
  22. How do you change the datatype of a column in SQL Server?
  23. SQL Server Query – groupwise multiplication
  24. Getting week number off a date in MS SQL Server 2005?
  25. How to reuse calculated columns avoiding duplicating the sql statement
  26. How do I move a table into a schema in T-SQL
  27. How to use RANK() in SQL Server
  28. How to set value to variable using ‘execute’ in t-sql?
  29. Update SQL with consecutive numbering
  30. Count number of records returned by group by

Leave a Comment