tricky update

[mcnewsxp]

i have a Results column that can contain data that looks like this:

XXX X
XXX XX/XXX
XXXX/XXX X/XX

so i want to parse this data into Results1, Results2, Results3 using the '/' as the delimiter.

can i do this in one pass with one sql statement? if so, example please.

tia,
mcnewsxp

[Gene Wirchenko]

On Thu, 12 Jul 2012 07:06:11 -0700 (PDT), mcnewsxp

Post by mcnewsxp
XXX X
XXX XX/XXX
XXXX/XXX X/XX
so i want to parse this data into Results1, Results2, Results3 using the '/' as the delimiter.
can i do this in one pass with one sql statement? if so, example please.

GIYF with "SQL Server split string".

Sincerely,

Gene Wirchenko

[mcnewsxp]

Post by Gene Wirchenko
On Thu, 12 Jul 2012 07:06:11 -0700 (PDT), mcnewsxp
>
>XXX X
>XXX XX/XXX
>XXXX/XXX X/XX
>
>so i want to parse this data into Results1, Results2, Results3 using the '/' as the delimiter.
>
>can i do this in one pass with one sql statement? if so, example please.
GIYF with "SQL Server split string".
Sincerely,
Gene Wirchenko

√

[Erland Sommarskog]

Post by mcnewsxp
XXX X
XXX XX/XXX
XXXX/XXX X/XX
so i want to parse this data into Results1, Results2, Results3 using the
'/' as the delimiter.
can i do this in one pass with one sql statement? if so, example please.

As long as you only have three fields and there is no period in the data,
parsename comes in handy:

; WITH CTE AS (
SELECT reverse(replace(col, '/', '.')) AS revcol
FROM tbl
)
SELECT reverse(parsename(revcol, 1)) AS results1,
reverse(parsename(revcol, 2)) AS results2,
reverse(parsename(revcol, 3)) AS results3
FROM CTE