How to determine whether a SQL string contains characters

How to determine whether a SQL string contains characters

In SQL, we can use many methods to determine whether a string contains a string, such as like, replace, and charindex functions. The following describes how to determine whether a string contains a string in SQL statements.

Flexible use of the CHARINDEX, PATINDEX, and wildcard Functions

Functions: CHARINDEX and PATINDEX

CHARINDEX: checks whether a character (string) is contained in another string, and returns the starting position of the specified expression in the string.

PATINDEX: Check whether a character (string) is contained in another string. Return the starting position of the first occurrence of a pattern in the specified expression; if this mode is not found in all valid text and character data types, zero is returned. Special: wildcards can be used!

Example:

1. query whether a string contains non-numeric characters

SELECT PATINDEX('%[^0-9]%', '1235X461')SELECT PATINDEX('%[^0-9]%', '12350461')

2. Check whether the string contains numeric characters.

SELECT PATINDEX('%[0-9]%', 'SUYLLGoO')SELECT PATINDEX('%[0-9]%', 'SUYLLG0O')

3. The function determines that a string contains only numbers.

CREATE FUNCTION [dbo].fn_IsNumeric(@pString VARCHAR(8000))RETURNS bitWITH ENCRYPTIONASBEGINDECLARE @vJudge intSET @vJudge = 0SELECT @vJudge = CASE WHEN PATINDEX('%[0-9]%', LOWER(@pString)) > 0 THEN 0WHEN PATINDEX('%[0-9]%', LOWER(@pString)) = 0 THEN 1ENDRETURN @vJudgeEND

4. The function determines that a string only contains letters (case-insensitive)

CREATE FUNCTION [dbo].fn_IsAlpha(@pString VARCHAR(8000))RETURNS bitWITH ENCRYPTIONASBEGINDECLARE @vJudge intSET @vJudge = 0SELECT @vJudge = CASE WHEN PATINDEX('%[a-z]%', LOWER(@pString)) > 0 THEN 0WHEN PATINDEX('%[a-z]%', LOWER(@pString)) = 0 THEN 1ENDRETURN @vJudgeEND

5. The function determines that the string does not contain any symbols (including spaces)

CREATE FUNCTION [dbo].fn_IsAlphanumeric(@pString VARCHAR(8000))RETURNS bitWITH ENCRYPTIONASBEGINDECLARE @vJudge intSET @vJudge = 0SELECT @vJudge = CASE WHEN PATINDEX('%[^a-z0-9]%', LOWER(@pString)) > 0 THEN 0WHEN PATINDEX('%[^a-z0-9]%', LOWER(@pString)) = 0 THEN 1ENDRETURN @vJudgeEND

6. The function determines that the string does not contain any symbols (except spaces)

Create function [dbo]. fn_IsAlphanumericBlank (@ pString VARCHAR (8000) RETURNS bitWITH ENCRYPTIONASBEGINDECLARE @ vJudge intSET @ vJudge = 0 SELECT @ vJudge = case when patindex ('% [^ a-z0-9] % ', LOWER (@ pString)> 0 THEN 0 when patindex ('% [^ a-z0-9] %', LOWER (@ pString) = 0 THEN 1 ENDRETURN @ vJudgeEND -- Note: [^ a-z0-9] mode has the last space.

Use charindex () -- charindex (character, string)> 0-> to view a section and the words contained in an article

select ID,title,author from Article where CHARINDEX(title,@item)>0

7. Use like --

select * from tablename where field1 like like ‘%key%'

8. Use the replace () function

Declare @ item nvarchar (100) set @ item = 'difficult English '; select ID, title, author from Article where LEN (REPLACE (@ item, title ,'')) <len (@ item); -- judge based on the replaced length> 2,

Summary

The preceding section describes how to judge the characters contained in strings in SQL statements. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!