How to match and extract text in a text field using a pattern?

[croberts21]

I'm using Power BI vMarch 2023, with a MS SQL Server database hosted in Azure so it's probably version 2022. Neither PBI nor MS SQL Server seems to support regular expressions (regex) or this would be much easier. 

I have a text field with multiple lines, each line separated by CRLF. This data is entered by humans so it is very inconsistent, which is why regex would be more accurate. I need to extract a piece of data from each text field. Lets call the text field "desc". I want to return a new column with the extracted text, I do not want to select records based on the text appearing in a field. I have more manipulations I need to do with the extrated text later. Here are examples of what could be in the field "desc".

***Do quickly.*** RUSH order. 45# Antique

See part af-ak-123. 60# white. 332 pages.

5.5 x 8.0, 99 pages, 50# B18 cream

I need to match the 2 digits plus the # sign, so I would match in each line: 

45#

60#

50#

respectively. ie. this would match a regex of /\d{2}#/.

The problems:

1. The text like "50#" could be anywhere in the text field separated by CRLF. 
2. MS SQL nor PBI supports regex. 
3. There is no other data in the field like "250#". It is always 2 digits followed by "#" sign. 

So how do I extract text like this? I could do it in MS SQL Server or Power Query. 

I have tried doing split but due to data inconsistency this is not accurate. 

Thank you.

EDIT: I'm very familiar with regex I just don't know how to do that in PBI or SQL Server on Azure. 

EDIT: This worked in SQL Server.

IIF(PATINDEX('%[0-9][0-9]#%',desc2)>0, SUBSTRING(desc2,PATINDEX('%[0-9][0-9]#%',desc2),3), '') as jobPaper

[christinepayton]

This sounds like something you'd use a regular expression for. You can use regular expressions with SQL Server: https://learn.microsoft.com/en-us/sql/ssms/scripting/search-text-with-regular-expressions?view=sql-s...

As an aside, ChatGPT is great at writing regular expressions for you, particularly if you tell it where you're planning to use it (e.g. it'll often provide python as the response, which you obviously don't want if you're trying to do it in SQL). 

[croberts21]

Thank you, yes I'd like to use Regular expressions (regex) which I'm very familiar with. I just don't know how to use Regex in T-SQL. 

Do you know how to use Regex to make a new column to extract the data I need in SQL Server? If the data does not exist in the text field then the resulting new column would be blank. 

I have found some options here but this is an old post. https://stackoverflow.com/questions/8928378/using-regex-in-sql-server?rq=3

[christinepayton]

I don't know how to do it in SQL Server, but I do know how to do it in Power Automate Desktop... PAD can run SQL queries, too, so that could potentially work. You'd do a for-each loop and for each row on the query use a parse text step, which has a checkbox that lets you paste in a regular expression. It'd probably be better to use SQL if you can figure that out though...