Microsoft SQL Server forum - Advance SQL question

Author

Advance SQL question

T.Kindermann

2005-06-23, 7:23 am

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
--------------
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf

3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
------
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----------
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
....

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
--------------------------------------------------------------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@GMX.DE without <x>

Razvan Socol

2005-06-23, 7:23 am

Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan

T.Kindermann

2005-06-29, 7:23 am

Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>
> Yes, it is possible:
>
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
>
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
>
> This queries work with up to 250 words in each row.
>
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
>
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
--------------------------------------------------------------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@GMX.DE without <x>

Sponsored Links