如何在SQL中拆分字符串 [英] How to split string in SQL
本文介绍了如何在SQL中拆分字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一个字符串就像(1:2~2:1,4,5~3:这是一个测试)
我想用第一个'〜'拆分它然后输出在sql中带':'的字符串
输出如
1 2
2 1,4,5
3这是一个测试
i希望它在SQL查询中是否可以在不使用函数或cte的情况下完成它
提前感谢。
我的尝试:
i已经尝试过。
并且还尝试了sql的SUBSTRING函数。
解决方案
SQL在字符串操作方面实际上非常差,所以如果你有办法做到这一点,不要在SQL中选择......
但是我在这里给你看一个选项,但请记住,那个在大数据上它会非常慢......
DECLARE @VAL AS NVARCH AR (MAX)= ' 1:2~2:1,4,5~3:这是测试' + ' 〜' - - 您必须确保字符串以行分隔符结尾
DECLARE @ ROWDEL AS NCHAR ( 1 )= ' 〜' - 行分隔符
DECLARE @ COLDEL AS NCHAR ( 1 )= ' :' - - 列分隔符
DECLARE @ RET TABLE
(
POS INT ,
VAL NVARCHAR (MAX)
)
WHILE (CHARINDEX( @ROWDEL , @ VAL )> 0 )
BEGIN
INSERT INTO @ RET (VAL)
SELECT SUBSTRING( @ VAL , 1 ,CHARINDEX( @ ROWDEL , @ VAL ) - 1 )
SET @ VAL = SUBSTRING( @ VAL ,CHARINDEX( @ ROWDEL , @ VAL )+ 1 ,LEN( @ VAL ))
END
更新 @ RET SET
POS = SUBSTRING(VAL, 1 ,CHARINDEX( @ COLDEL ,VAL) - 1 ),
VAL = SUBSTRING(VAL,CHARINDEX( @ COLDEL ,VAL)+ 1 ,LEN(VAL))
FROM @ RET
SELECT * FROM @ RET
Google搜索为您提供了大量答案:如何分割SQL中的字符串 [ ^ ]
a string is like (1:2~2:1,4,5~3:this is a test)
and i want to split it with first '~' AND THEN the output string with ':' in sql
output like
1 2
2 1,4,5
3 this is a test
i want it in SQL query is it possible to do it without using function or cte
thanks in advance.
What I have tried:
i have tried case when.
and also tried SUBSTRING function of sql.
解决方案
SQL is actually very poor at string manipulation, so if you have a way to do it NOT in SQL pick that...
However I show you here an option, but remember, that on large data it will be terribly slow...
DECLARE @VAL AS NVARCHAR(MAX) = '1:2~2:1,4,5~3:THIS IS A TEST' + '~' -- you must ensure the string ends with the row delimiter DECLARE @ROWDEL AS NCHAR(1) = '~' -- row delimiter DECLARE @COLDEL AS NCHAR(1) = ':' -- column delimiter DECLARE @RET TABLE ( POS INT, VAL NVARCHAR(MAX) ) WHILE (CHARINDEX(@ROWDEL, @VAL) > 0) BEGIN INSERT INTO @RET(VAL) SELECT SUBSTRING(@VAL, 1, CHARINDEX(@ROWDEL, @VAL) - 1) SET @VAL = SUBSTRING(@VAL, CHARINDEX(@ROWDEL, @VAL) + 1, LEN(@VAL)) END UPDATE @RET SET POS = SUBSTRING(VAL, 1, CHARINDEX(@COLDEL, VAL) - 1), VAL = SUBSTRING(VAL, CHARINDEX(@COLDEL, VAL) + 1, LEN(VAL)) FROM @RET SELECT * FROM @RET
Google Search has lots of answers for you: How to split string in SQL[^]
这篇关于如何在SQL中拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文