How to extract from text field, numbers like "50#" and "45#"?

[croberts21]

I have Power BI Desktop version March 2023 connecting to an Sql Server on Azure, so it should be the latest version 2022. 

I'm trying to extract a string as a new column from a long text field 2 digits followed by a "#" pound sign, call it "mydesc", which has multiple lines (CRLF) in it. I don't want to just return rows where a column matches a string, I want to extract the string found as a new column. I'm using a SELECT statement.  Examples we have are "60#", "50#", and "45#". We only have those 3 values so far. 

I'm trying to use LIKE but get an error. Here is what I've tried:

mydesc LIKE '[0-9][0-9]#' as rePaper,
mydesc LIKE '[0-9][0-9]#',
mydesc LIKE '[0-9][0-9]\#' as rePaper,

The error is: "DataSource.Error: Microsoft SQL: Incorrect syntax near the keyword 'LIKE'."

I don't see the problem through I do not use LIKE very much. SQL Server does not support regex natively or I would use that. 

Any ideas how I can get this to work, with or without LIKE? Thank you!

[OwenAuger]

Hi @croberts21 

Here is a sample query to illustrate one method using PATINDEX:

SELECT
SUBSTRING ( mydesc, PATINDEX('%[0-9][0-9]#%', mydesc), 3 ) as repaper
FROM mytable

Does this work for you?

Regards

[croberts21]

It works partially. For text fields that contain "50#" or "45#" it does extract "50#" or "45#". But for text fields that do not contain [0-9][0-9]# it seems to extract the first 2 characters. Here is some example input and output

Input	Output	Correct?
PO/more stuff here, there is no paper weight here	PO	No, should return blank
blah blah 50# paper more stuff	50#	Yes
writing more stuff CRLF 45#	45#	Yes
60# paper	60#	Yes

This formula worked!

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

PATINDEX will return >0 if it finds the pattern otherwise it will return 0.

[OwenAuger]

Sure thing, the above code didn't handle failed matches, in which case PATINDEX returns 0 😅

You could handle it like this:

SELECT CASE 
    WHEN PATINDEX('%[0-9][0-9]#%', mydesc) = 0
      THEN NULL
    ELSE SUBSTRING(mydesc, PATINDEX('%[0-9][0-9]#%', mydesc), 3)
    END AS repaper
FROM mytable

Disclaimer: I'm not a SQL specialist 🙂

Regards