How to determine whether a SQL string contains characters

Source: Internet
Author: User

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.