-1

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

Jewel
  • 1
  • 1
  • Not sure on the exact answer but I'd create a SQL function that returns the value either to the left or right of the separator. That function does the complicated work, then it will be simple for use in a query. – Etienne Charland Nov 23 '22 at 03:43
  • This should help: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – sa-es-ir Nov 23 '22 at 03:43
  • You can probably do with SUBSTRING and CHARINDEX in a custom function. – Etienne Charland Nov 23 '22 at 03:44

2 Answers2

0
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.

J.D.
  • 954
  • 6
  • 22
0

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
RF1991
  • 2,037
  • 4
  • 8
  • 17
  • 1
    Note this may have unexpected results when any other text passed in contains a period in it, that's meant to be part of the text. E.g. `www.CorporateServices.com\Corporate Affairs & Communications(DP19)`. Of course, OP provided very little context, so who knows if it matters to them. Interesting use of `PARSENAME()` nonetheless. – J.D. Nov 23 '22 at 13:49