将varchar列的值分为两列

本文介绍了将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;