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

First build a test sheet:

CREATE table teststring (name nvarchar (100));
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 (', ', name) 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 table teststring2 (name nvarchar (100));

declare @name nvarchar (100);
Select @name = name from teststring;
while (CHARINDEX (', ', @name) <>0)
Begin
Insert INTO Teststring2 Select SUBSTRING (@name, 1,charindex (', ', @name)-1);
Set @name =stuff (@name, 1,charindex (', ', @name), ");
Set @[email protected]+1;
End
INSERT INTO Teststring2
Select @name

The SELECT * from Teststring2 can be seen.

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

Table variables I will not, used once, used to forget, and occasionally used once! It's good to know that there's something that you can think of at least when you're having problems thinking.

The above are based on my recent time to do the company, probably the PHP Web page, the Name text box needs to enter the user name, but one time input, you have to add more than once, if I put the background database name This field is set larger, one can enter more than one name, but the name and name separated by commas, I can handle it in the database.

Have time to write about my own do the time Attendance entry page.

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