Determining where strings appear and how to intercept strings in SQL Server

First build a test sheet:

Create Table nvarchar (+)); Insert  into teststring Values (' Zhang San, John Doe, Harry, Caifan, Xiao Rambler, royal palace ');

1. Determine the number of occurrences of a character (string) in a string, the position where the first occurrence occurs last:

View ', ' The number of occurrences

Select LEN (name) - LEN (REPLACE(name,',', ' from teststring;

View ', ' the first occurrence of the location:

Select CHARINDEX (',' from teststring;

View ', ' last seen location:

Select Len (name) - (CHARINDEX(',',REVERSE(name))-1 from teststring;

2. Remove the character (string) separated by ', ', in this case, ' Zhang San ', ' John Doe ', etc.

This is a regular, first of all should think of there is no specific system function implementation, as if no, second should think of loops.

When I take ' Zhang San ' out, how do you remove ' Zhang San '?

It's not hard to take ' Zhang San '

Select SUBSTRING (Name,1,CHARINDEX(',', name)-1 from teststring;

How to get rid of ' Zhang San '? At first I do not know, Baidu, Google ah, the keyword SQL, split bar, anyway, I also search

Select STUFF (Name,1,CHARINDEX(',', name),'from teststring;

It seems to be possible with replace.

Select REPLACE (Name,substring(name,1,CHARINDEX(',', name)),"  from TestString;

That is now the cycle, I seldom write stored procedures or functions, grammar do not remember, when used to check (often used to remember), I write anonymous stored procedures, haha

Create TableTeststring2 (namenvarchar( -));Declare @name nvarchar( -);Select @name=Name fromteststring; while(CHARINDEX(',',@name)<>0)beginInsert  intoTeststring2Select SUBSTRING(@name,1,CHARINDEX(',',@name)-1);Set @name=STUFF(@name,1,CHARINDEX(',',@name),"');Set @i=@i+1;EndInsert  intoteststring2Select @name

The SELECT * from Teststring2 can be seen.

This is only one field in the table, if more, use the table variable should be.

The following example:

  --when the product name contains parentheses, the bracketed content is truncated from the product name  SELECTProduct_code, Case  when CHARINDEX('(', Product_Name)!= 0  Then       substring(Product_Name,CHARINDEX('(', Product_Name)+1,CHARINDEX(')', Product_Name)-CHARINDEX('(', Product_Name)-1)        End  asPcode, Product_Name fromProduct

Determining where strings appear and how to intercept strings in SQL Server