DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Issue :
Extract numbers or letters in a string
We often encounter the problem of extracting specific content in a string, such as extracting numbers in a string, or extracting letters in a string.
Here is a simple way to extract numbers or letters in a string. If you want to extract other specific content in the string, you only need to make simple modifications based on this method.
Solution:
First, let’s look at how to extract numbers from a string. Introduce through the following example.
Step1: create sample data:
-Test date
CREATE TABLE #test(string varchar(25))
INSERT INTO #test VALUES('HUEWAFDHI734267')
,('34724632DHeufbUDS')
,('dhd36d4DH743dre')
SELECT * FROM #test
Step2: create a custom function.
CREATE FUNCTION dbo.Extractnum(@str VARCHAR(MAX))
RETURNS INT
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@str)>0
BEGIN
SET @str=STUFF(@str,PATINDEX('%[^0-9]%',@str),1,'')
END
RETURN CAST(@str AS INT)
END
Step3: call this function
SELECT dbo.Extractnum(string) AS Num
FROM #test
As you can see from the screenshot, no matter where the number is in a string, it can be extracted through a custom function.
Next, let us look at the method of extracting letters in a string. In fact, the method of extracting letters in a string is like the method of extracting numbers. In other words, the method of extracting specific content in a string is similar.
We still use the test data when extracting numbers:
Step1: create a custom function.
CREATE FUNCTION dbo.Extractlet(@str VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
WHILE PATINDEX('%[^A-Z]%',@str)>0
BEGIN
SET @str=STUFF(@str,PATINDEX('%[^A-Z]%',@str),1,'')
END
RETURN CAST(@str AS VARCHAR(MAX))
END
Step2: call this function
SELECT dbo.Extractlet(string) AS Letter
FROM #test
As you can see from the screenshot, when extracting the letters in the string, the custom function can extract all the letters whether they are uppercase or lowercase letters.
For more details, please refer to:
PATINDEX (Transact-SQL)
STUFF (Transact-SQL)