Splitting value of a varchar column into two colum

问题:

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

I have seen CHARINDEX and REVERSE on MSDN but haven't been able to put together a soltuion.

How can I best split a varchar/string column value into 2 columns in a result set in TSQL ?

回答1:

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

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;

回答2:

what about using PARSENAME function in a tricky way?

USE tempdb;
GO

CREATE TABLE #names 
(
      id int NOT NULL PRIMARY KEY CLUSTERED
    , name varchar(30) NOT NULL
);
GO

INSERT INTO #names (id, name)
VALUES 
    (1, 'DB5697\DEV'),
    (2, 'DB5800\STG'),
    (3, 'DB5900\PRD');
GO

SELECT
      name
    , PARSENAME(REPLACE(name, '\', '.'), 2) AS [Server]
    , PARSENAME(REPLACE(name, '\', '.'), 1) AS [Instance]
FROM
    #names;
GO

DROP TABLE #names;
GO

The PARSENAME function accepts 2 parameters and gets the name part of a fully qualified name. The second parameter is the part name enumerator.

Value 2 is for SCHEMA and 1 is for OBJECT.

So, with the REPLACE function the "\" char is replaced by "." in order to have a SCHEMA.OBJECT format of your SERVERNAME\INSTANCE values. Then, PARSENAME behave like having a simple object name in the string.

回答3:

You can use CHARINDEX to check for the character position of the splitter ('/') and use SUBSTRING to split the string. However care has to be taken to ensure you handle records without splitters else you would invoke an error. Also in the case where splitter is unavailable, decision has to be made as to which column the data should be mapped to. Here I am mapping data to FirstName and assigning NULL to LastName

DECLARE @TableBuyer TABLE (ID INT, FullName VARCHAR(100))

INSERT INTO @TableBuyer
SELECT '1','Bryan/Greenberg' UNION ALL
SELECT '2','Channing/Tatum' UNION ALL
SELECT '3','Paul/William' UNION ALL
SELECT '4','EricBana' UNION ALL
SELECT '5','James/Lafferty' UNION ALL
SELECT '6','Wentworth/Miller'

SELECT
       CASE
       WHEN CHARINDEX('/', FullName) > 0 THEN SUBSTRING(FullName, 1, CHARINDEX('/', FullName) - 1)
       ELSE FullName
       END AS FirstName
       ,
       CASE
       WHEN CHARINDEX('/', FullName) > 0 THEN SUBSTRING(FullName, CHARINDEX('/', FullName) + 1, LEN(FullName))
       ELSE NULL
       END AS LastName
FROM @TableBuyer;

回答4:

DECLARE @TableBuyer TABLE (ID INT, FullName VARCHAR(100))

INSERT INTO @TableBuyer
SELECT '1','Bryan/Greenberg' UNION ALL
SELECT '2','Channing/Tatum' UNION ALL
SELECT '3','Paul/William' UNION ALL
SELECT '4','EricBana' UNION ALL
SELECT '5','James/Lafferty' UNION ALL
SELECT '6','Wentworth/Miller'


select left(FullName, len(FullName)-CHARINDEX('/', REVERSE(FullName))) as firstname,
substring(FullName, len(FullName)-CHARINDEX('/', REVERSE(FullName))+ 2, len(FullName)) as lastname
from @TableBuyer

OR

select left(FullName, len(FullName)-CHARINDEX('/', REVERSE(FullName))) as firstname,
RIGHT(FullName, len(FullName)-CHARINDEX('/', FullName)) as lastname
from @TableBuyer

回答5:

There is no "simple" method. Something like this should work:

select left(col, charindex('\', col) - 1) as column1,
       right(col, charindex('\', reverse(col)) - 1) as column2

You might need to double up on the backslash ('\\') to get it to work properly.