how to get the words after the slash in SQL query-CodePudding

I have a date which look like this "Corporate Services\Corporate Affairs & Communications(DP19)"

I want to the result to be like this:

Column A	Column B
Corporate Service	Corporate Affairs & Communications (DP19)

I already tried using substring but no luck, I am using Microsoft SQL

CodePudding user response：

DECLARE @AData VARCHAR(1000) = 'Corporate Services\Corporate Affairs & Communications(DP19)';

SELECT
    LEFT(@AData, CHARINDEX('\', @AData) - 1) AS [Column A],
    SUBSTRING(@AData, CHARINDEX('\', @AData)   1, LEN(@AData)) AS [Column B];

I kind of cheated with using LEN(@AData) for the Length parameter of the SUBSTRING() function used in Column B, but SUBSTRING() doesn't care, so no harm no foul:

length

...If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

CodePudding user response：

you can use PARSENAME

declare @t table(ch varchar(max))
insert into @t values
(N'Corporate Services\Corporate Affairs & Communications(DP19)');

SELECT 
PARSENAME(REPLACE(ch,'\','.'),2) AS N'Column A' ,
PARSENAME(REPLACE(ch,'\','.'),1) AS N'Column B'
FROM @t