本文介绍了将varchar列的值分为两列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我有一列中的字符串长度有所不同,但它们中都包含斜杠\, 我如何选择让一列显示\之前的所有内容,而另一列显示\之后的所有内容?
If I have a column in which strings vary in length but they ALL have a slash \ within, how can I SELECT to have one column display everything BEFORE the \ and another column displaying everything AFTER the \?
name column1 column2 DB5697\DEV DB5697 DEV我已经在MSDN上看到了CHARINDEX和REVERSE,但是还不能解决问题.
I have seen CHARINDEX and REVERSE on MSDN but haven't been able to put together a soltuion.
在TSQL中,如何最好地将varchar/string列值拆分为2列?
How can I best split a varchar/string column value into 2 columns in a result set in TSQL ?
推荐答案以下内容如何?( SQL小提琴 ):
How about the following (SQL Fiddle):
SELECT m.name, LEFT(m.name, CHARINDEX('\', m.name) - 1) AS column1, RIGHT(m.name, LEN(m.name) - CHARINDEX('\', m.name)) AS column2 FROM MyTable m如何处理其中没有\的字符串( SQL Fiddle ):
How to handle strings with no \ in them (SQL Fiddle):
SELECT m.name, CASE WHEN CHARINDEX('\', m.name) = 0 THEN '' ELSE LEFT(m.name, CHARINDEX('\', m.name) - 1) END AS column1, CASE WHEN CHARINDEX('\', m.name) = 0 THEN '' ELSE RIGHT(m.name, LEN(m.name) - CHARINDEX('\', m.name)) END AS column2 FROM MyTable m;更多推荐
将varchar列的值分为两列
发布评论