SQL 小笔记

1. 查询某个字符串中“，”(或其他符号，以第一次出现的为准)后面的字符串

　　select left('1233,abdc',Charindex(',','1233,abdc')-1) number

　　　　,substring('1233,abdc',CHARINDEX(',','1233,abdc')+1,600) lettle
      执行结果：

2.将某个字符串以“，”（或其他符号）分隔为单独的字符串

declare @SplitStringsTable
TABLE([value] varchar(8000))
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText varchar(8000);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len('123,3434,asdv')) ---此处为要分隔的字符串
    BEGIN
        SELECT @NextIndex=charindex(',','123,3434,asdv',@CurrentIndex);---“，”为分隔标准
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=len('123,3434,asdv')+1;       
        SELECT @ReturnText=substring('123,3434,asdv',@CurrentIndex,@NextIndex-@CurrentIndex);

        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText);       
        SELECT @CurrentIndex=@NextIndex+1;
    END
select * from @SplitStringsTable
输出结果：Value

　　　　　123
　　　　　3434
　　　　　asdv   

3.数据库数据格式为多个ID号以“，”隔开串成字符串存储，查询该字段中存储的ID对应的Name

（可先使用上述方法2创建Split函数传入参数为字符串和分隔符，将ID号分别读取出来，再根据ID查询对应Name，再以“，”隔开串成字符串）

select STUFF( (SELECT B.EmployeeName + ',' FROM RL_Employee B WHERE B.EmployeeCode
    IN (SELECT VALUE FROM Split (E.OtherMananger,',')) FOR XML PATH('')
    ),1,0,'')  AS OtherManangerName
    from RL_Employee E where E.OtherMananger<>''