charindex function and reverse function usage in SQL

Source: Internet
Author: User

   ----------------------First introduce the CHARINDEX function-----------------------------                                                                                                                                                                                                                           The    charindex function returns the starting position of a character or string within another string. The    charindex function call method is as follows:

CHARINDEX (expression1, expression2 [, start_location])

Expression1 is the character to look for in expression2, Start_location is where the CHARINDEX function begins to find expression2 in expression1.

The CHARINDEX function returns an integer that is the position of the string to find in the string being searched for. If CHARINDEX does not find the string to find, then the function integer "0". Let's take a look at the result of the following function command execution:

CHARINDEX (' sql ', ' Microsoft sql Server ')

This function command returns the starting position of SQL in Microsoft SQL Server, in which case the CHARINDEX function returns "S" in Microsoft SQL Server location 11.
Next, let's look at this charindex command:

CHARINDEX (' 7.0 ', ' Microsoft sql Server ')

In this example, CHARINDEX returns zero because the string "7.0" cannot be found in Microsoft SQL Server. Next, let's take a look at two examples of how to use the CHARINDEX function to solve the actual T-SQL problem.

------------------------Here is the reverse function------------------------------

The reverse function returns the character reversal order of the string str.

The reverse function call method is as follows:

Sql--> Select reverse (' ABCD ');

--->REVERSE (' ABCD ')

--->DCBA

Comprehensive application of--------------------charindex,reverse,substring function: Ms_sql gets the string and position of the string that appears at the end-----------------

A such as: ' 6.7.8.2.3.4.x ' get last '. ' The following string:

DECLARE @str1 varchar (50)

Set @str1 = ' 6.7.8.2.3.4. x'

Select REVERSE (SUBSTRING (REVERSE (@str1), 1,charindex ('. ', REVERSE (@str1))-1) --------string: ' X '--

------>>>----

Two such as: ' 6.7.8.2.3.4.x ' get last '. ' The preceding string:

DECLARE @str2 varchar (50)

Set @str2 = ' 6.7.8.2.3.4.x '

SELECT substring (@str2, 1, (LEN (@str2)-charindex ('. ', REVERSE (@str2) ))--------string: ' 6.7.8.2.3.4 '--

------->>>----

Three such as: ' 6.7.8.2.3.4.x ' get last '. ' In the position of the string:

DECLARE @str3 varchar (50)

Set @str3 = ' 6.7.8.2.3.4.x '

SELECT LEN (@str3)-charindex ('. ', REVERSE (@str3)) +1 --------integer:12--

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.