How to extract letters from a string in SQL

This article shows you how to extract letters from a string in SQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Concrete solution

We create a function and call this function to find all the letters.

CREATE FUNCTION dbo.GET_LETTER (@ Str VARCHAR) RETURNS VARCHAR (100) AS BEGIN WHILE PATINDEX ('% [^ a murz]%', @ Str) > 0 BEGIN SET @ Str = STUFF (@ Str, PATINDEX ('% [^ a murz]%', @ Str), 1,'); END; RETURN @ Str; END; GO

Code interpretation

The above solution mainly uses two functions, PATINDEX function and STUFF function.

PATINDEX function

PATINDEX ('% pattern%', expression)

Returns the position of the first occurrence of the pattern string in the expression expression, starting with 1.

For example

SELECT PATINDEX ('% SQL%','SQL database development')

Results:

Because SQL is in the first place, the return result is 1

STUFF function

STUFF (expression1, start, length, expression2)

The string expression1 starts at the start position, deletes a character of length length, and fills in expression2 after start.

For example

SELECT STUFF ('SQL, developer', 4pc1 'database')

Results:

The above example is to delete "," and replace it with "database".

Test function

After understanding the above function, let's test our custom function GET_LETTER ()

SELECT dbo.GET_LETTER ('SQL number 1 according to 2 library 3 open 4 road')

Results:

This is consistent with our expected results, which proves that this custom function is feasible.

The above is how to extract letters from a string in SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.