SQL converts (n) varchar type to a collection of multiple columns or multiple value

Sometimes houyouxi may require multiple parameters when queried by the server, and the number of bits is not necessarily dynamic.

This could be a condition for passing a list to SQL.

---- split a list and return a table if ids-- used to  take a comma separated list as a string and return a &NBSP;TABLE&NBSP;--CREATE&NBSP;FUNCTION&NBSP;[DBO]. [Splitidlist] (     @CommaSeparatedIDList  nvarchar (+))  RETURNS  @List  TABLE  (     id int) begin    declare  @sItem  varchar (Ten);     while charindex (', ', @CommaSeparatedIDList, 0)  <> 0     BEGIN        SELECT              @sItem =rtrim (LTRIM (SUBSTRING (@CommaSeparatedIDList, 1,charindex (', ', @ commaseparatedidlist,0)-1)),             @ Commaseparatedidlist=rtrim (LTRIM (SUBSTRING (@CommaSeparatedIDList, CHARINDEX (', ', @CommaSeparatedidlist,0) +1,len (@CommaSeparatedIDList)));         if  LEN (@sItem)  > 0            insert  INTO  @List  SELECT  @sItem;     end;    if len ( @CommaSeparatedIDList)  > 0        insert into @ list select  @CommaSeparatedIDList; -- put the last item in     returnendgo

Take a look at the example:

DECLARE @test NVARCHAR (500); SET @test = ' 1,2,3,9,4 '; SELECT *from [splitidlist] (@test);

Output:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7D/8F/wKioL1bq1kfi4ZieAAAHLfG0Uk0584.png "title=" original "alt = "Wkiol1bq1kfi4zieaaahlfg0uk0584.png"/>

The CHARINDEX function returns the starting position of a character or string in another string

There is a similar function: PATINDEX

These two functions are often used to search for characters or strings in a word. If the character being searched contains a character to search for, the two functions return a nonzero integer, which is the beginning of the character to be searched in the character being searched.

The PATINDEX function supports searching with wildcard characters

CHARINDEX does not support wildcard characters

Also attached is a more general practice from MSDN:

Create function dbo. Ufn_separates_columns (@TEXT       varchar (8000),  @COLUMN      TINYINT,  @SEPARATOR  char (1)) Returns varchar (8000) As     begin          DECLARE  @POS_START  INT= 1;          DECLARE  @POS_END  int= charindex (@SEPARATOR,  @ text,  @POS_START)          while (@COLUMN  > 1                AND  @POS_END  > 0)              BEGIN                  SET  @POS_ start =  @POS_END  + 1;           &NBsp;     set  @POS_END  = charindex (@SEPARATOR,  @TEXT,  @POS_ START);                  set  @COLUMN  =  @COLUMN  - 1;              END;         IF  @COLUMN  >  1             SET  @POS_START   = len (@TEXT)  + 1;         IF  @POS_END  =  0             SET  @POS_END  =  len (@TEXT)  + 1;         return substring (@ text,  @POS_START,  @[email protected]_start);     end; GO

Test:

DECLARE @IMPORTROW varchar, @CD_PERSON INT, @NM_MAIL VARCHAR, @DT_CREATED DATE; SET @IMPORTROW = ' 154198|2014-01-08| [Email protected]| Comments|123456|2015-10-30| '; SELECT @CD_PERSON = dbo. Ufn_separates_columns (@IMPORTROW, 1, ' | '), @DT_CREATED = CAST (dbo. Ufn_separates_columns (@IMPORTROW, 2, ' | ') As DATE), @NM_MAIL = dbo. Ufn_separates_columns (@IMPORTROW, 3, ' | ');  --through these VARIABLE, you CAN change--your DATA after BEING separatedselect @CD_PERSON as Cd_person, @NM_MAIL as Nm_mail, FORMAT (@DT_CREATED, ' mm/dd/yyyy ') as dt_created; GO

Results:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7D/92/wKiom1bq1wHjhO-DAAAM1p3Hqog378.png "title=" 2016-03-18_001019.png "alt=" Wkiom1bq1whjho-daaam1p3hqog378.png "/>

This article is from the "Wolf Soul" blog, please be sure to keep this source http://lybing.blog.51cto.com/3286625/1752372

SQL converts (n) varchar type to a collection of multiple columns or multiple value