declare @str nvarchar(300)--目标字符串 ,@flag nvarchar(30) ,@index int--‘-’的下标 ,@splitStr nvarchar(300)--截取的字符串 set @index=CHARINDEX('-',@str) while(@index<>0)--当还有‘-’的标记在时 BEGIN set @splitStr=@str set @flag=LEFT(@splitStr,@index-1)--获取截取的值 set @splitStr = substring(@splitStr,@index+1,LEN(@splitStr))--截取之后的值 去除第一个‘-’ set @index=CHARINDEX('-',@splitStr)--获取‘-’的下标 END
DECLARE @s VARCHAR(max)='ewrewrw111-dddddd-243423423-****03' DECLARE @xml XML SET @xml='<r><n>'+REPLACE(@s,'-','</n><n>')+'</n></r>' SELECT b.value('.','varchar(max)') FROM @xml.nodes('r/n') AS s(b) /* ewrewrw111 dddddd 243423423 ****03 */
DECLARE @str VARCHAR(100)='289EA224-F257-44D3-8907-B3172554AD91-';WITH CTE AS ( SELECT LEFT(@str,CHARINDEX('-',@str)-1) AS VAL,RIGHT(@str,LEN(@str)-CHARINDEX('-',@str)) AS A UNION ALL SELECT LEFT(A,CHARINDEX('-',A)-1) AS VAL,RIGHT(A,LEN(A)-CHARINDEX('-',A)) AS A FROM CTE WHERE LEN(A)>0 ) SELECT VAL FROM CTE VAL ---------------------------------------------------------------------------------------------------- 289EA224 F257 44D3 8907 B3172554AD91(5 row(s) affected)
DECLARE @Str NVARCHAR(2000)='“******-******-******-****03'SELECT PARSENAME(Str1,4),PARSENAME(Str1,3),PARSENAME(Str1,2),PARSENAME(Str1,1) FROM (SELECT Str1=REPLACE(@Str,'-','.')) AS a /* (无列名) (无列名) (无列名) (无列名) “****** ****** ****** ****03 */
,@flag nvarchar(30)
,@index int--‘-’的下标
,@splitStr nvarchar(300)--截取的字符串
set @index=CHARINDEX('-',@str)
while(@index<>0)--当还有‘-’的标记在时
BEGIN
set @splitStr=@str
set @flag=LEFT(@splitStr,@index-1)--获取截取的值
set @splitStr = substring(@splitStr,@index+1,LEN(@splitStr))--截取之后的值 去除第一个‘-’
set @index=CHARINDEX('-',@splitStr)--获取‘-’的下标
END
DECLARE @s VARCHAR(max)='ewrewrw111-dddddd-243423423-****03'
DECLARE @xml XML
SET @xml='<r><n>'+REPLACE(@s,'-','</n><n>')+'</n></r>'
SELECT b.value('.','varchar(max)') FROM @xml.nodes('r/n') AS s(b)
/*
ewrewrw111
dddddd
243423423
****03
*/
PARSENAME(REPLACE('12345sfaf_2342onaov_2ono2o34_joafone','_','.'),2),
PARSENAME(REPLACE('12345sfaf_2342onaov_2ono2o34_joafone','_','.'),3),
PARSENAME(REPLACE('12345sfaf_2342onaov_2ono2o34_joafone','_','.'),4)
SELECT LEFT(@str,CHARINDEX('-',@str)-1) AS VAL,RIGHT(@str,LEN(@str)-CHARINDEX('-',@str)) AS A
UNION ALL
SELECT LEFT(A,CHARINDEX('-',A)-1) AS VAL,RIGHT(A,LEN(A)-CHARINDEX('-',A)) AS A
FROM CTE
WHERE LEN(A)>0
)
SELECT VAL FROM CTE
VAL
----------------------------------------------------------------------------------------------------
289EA224
F257
44D3
8907
B3172554AD91(5 row(s) affected)
FROM
(SELECT Str1=REPLACE(@Str,'-','.')) AS a
/*
(无列名) (无列名) (无列名) (无列名)
“****** ****** ****** ****03
*/