Help on "splitting up" data in a field

Hi
I'm having a problem finding out how I can split data in one field and
then use the values to match records in another table.
In table 1, I have a field where the values looks like
e.g. "229 231 233 235". What I'd like to do, is to match these 4
numbers with an ID in table 2 to get the values from table2. I.e. I'd
like to split up this one value to 4 values (229, 232, 233,235).
I've tried to use REPLACE to put in a "," between each so I could use it
as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
Has any of you any other suggestions to how it can be done? It's not
always the same number of numbers in the field (e.g. another one could
be "456 29580010" ). The field is a VARCHAR(1000) and there're also
some text strings in it. These seems to be some old crab though and I
don't need these values.
The only "general" thing with the formatting, seems to be that there are
2 spaces between each of the numbers I'd like to get out, so I think I
can use that as a "delimiter".
Anyone who has some hints to this?
Regards
SteenSteen
Take a look at Anith's script
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> Steen
> Take a look at Anith's script
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
>
>
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Thanks Uri
I must admit, that I can't really see the purpose of the script, and
also I can't see how it can be used to solve my problem.
I've tried to see if I could get some ideas from the script, but I can't
really see how I can use it?
Regards
Steen|||In SQL 2000 you have to properly normalize the data - i.e. parse the values
and store them in a new table or redesign the table.
In SQL 2005 you can use Anith's function to parse the values on-the-fly
using CROSS APPLY.
ML
http://milambda.blogspot.com/|||I'm really sorry Steen , by posting Anith's example I did mean to give you
an idea to solve the problem
See if this helps you
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE TABLE #Source (col1 INT NOT NULL)
CREATE TABLE #Target (col1 INT NOT NULL)
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 229 1 22'
--Inserting the values to the source table
INSERT INTO #Source
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Source
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5 33 10 1 22'
--Inserting the values to the Target table
INSERT INTO #Target
SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
from numbers where substring(' '+@.Ids,n,1)=' '
AND n < LEN(@.Ids) + 1
SELECT * FROM #Target
-->>> e.g. "229 231 233 235". What I'd like to do, is to match these 4
SELECT * FROM #Source WHERE NOT EXISTS
(SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
> Uri Dimant wrote:
> Thanks Uri
> I must admit, that I can't really see the purpose of the script, and also
> I can't see how it can be used to solve my problem.
> I've tried to see if I could get some ideas from the script, but I can't
> really see how I can use it?
> Regards
> Steen|||I think this might help you out, but there are problems with the aproach...
For one, the string concatenation leaves you open to SQL injection.
Granted, because you are selecting the values from a table, any malicious
injection code needs to actually be stored in your table, but it is still a
possibility. Second, this assumes you are dealing with numeric values, if
you need character values you will have to add in quotes along with the
commas.
declare @.SelectString varchar(1000)
set @.SelectString = TABLE1.FIELD1
set @.SelectString = replace(@.SelectString,' ',',')
set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
@.SelectString + ')'
EXECUTE sp_executesql @.SelectString
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd
> like to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could
> be "456 29580010" ). The field is a VARCHAR(1000) and there're also
> some text strings in it. These seems to be some old crab though and I
> don't need these values.
> The only "general" thing with the formatting, seems to be that there are
> 2 spaces between each of the numbers I'd like to get out, so I think I
> can use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||Uri Dimant wrote:
> I'm really sorry Steen , by posting Anith's example I did mean to give you
> an idea to solve the problem
> See if this helps you
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> CREATE TABLE #Source (col1 INT NOT NULL)
> CREATE TABLE #Target (col1 INT NOT NULL)
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 229 1 22'
> --Inserting the values to the source table
> INSERT INTO #Source
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
> SELECT * FROM #Source
>
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5 33 10 1 22'
> --Inserting the values to the Target table
> INSERT INTO #Target
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(' ', @.Ids + ' ', n) - n)
> from numbers where substring(' '+@.Ids,n,1)=' '
> AND n < LEN(@.Ids) + 1
>
> SELECT * FROM #Target
>
> -->>> e.g. "229 231 233 235". What I'd like to do, is to match these
4
> SELECT * FROM #Source WHERE NOT EXISTS
> (SELECT * FROM #Target WHERE #Source.col1=#Target.col1)
>
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:%233vpwsPHGHA.516@.TK2MSFTNGP15.phx.gbl...
>
Thanks for you input. I'll have to look further at the example. Right
now I still can't figure out how I can use it, but I'll check it out
tomorrow with a "fresh" pair of eyes..:-).
REgards
Steen|||Jim Underwood wrote:
> I think this might help you out, but there are problems with the aproach..
.
> For one, the string concatenation leaves you open to SQL injection.
> Granted, because you are selecting the values from a table, any malicious
> injection code needs to actually be stored in your table, but it is still
a
> possibility. Second, this assumes you are dealing with numeric values, if
> you need character values you will have to add in quotes along with the
> commas.
> declare @.SelectString varchar(1000)
> set @.SelectString = TABLE1.FIELD1
> set @.SelectString = replace(@.SelectString,' ',',')
> set @.SelectString = 'select fieldlist from table2 where table2.id in (' +
> @.SelectString + ')'
> EXECUTE sp_executesql @.SelectString
>
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
>
Hi Jim
The script is only for my own use, so I'm not so worried about
injections. It's just for producing some check lists to a few users.
I'll check out your script to see if it works. I'm having both numeric
and text values in the field, so I'll have to remove the text strings first.
Regards
Steen|||There is a function called charindex() which returns the numeric position of
one string within another string. You can join the two tables using
charindex, so that each row in MyTableA is joined with 0 - many rows in
MyTableB where charindex( .. ) > 0.
select
MyTableA.IDS,
MyTableB.ID
from MyTableA
left join MyTableB
on charindex(' '+MyTableB.ID+' ',' '+MyTableA.IDS+' ') > 0
The issue is that this data model is not properly normalized because it is
storing multiple values in one column:
http://www.agiledata.org/essays/dat...html#Normalize
This presents in at least 3 problems:
1. Accuracy: Can you depend on the format of the delimited values
reliable? The purpose of appending additional spaces before and after the
strings is to insure that:
charindex('999','123 ABC999 456') = 0
2. Performance: A non indexed table scan will probably be used due to
using a function for the join expression
http://www.microsoft.com/technet/pr...s/c0618260.mspx
http://www.sql-server-performance.c...ing_indexes.asp
3. Your queries will be more complex to write.
Let's assume that you have a Customer table and a Discount table.What is
needed is a reference table called CustomerDiscount that associates 0 - many
promotions for each customer.
For example:
CustomerID PromotionID
200 10
200 11
212 10
212 13
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:OFJZlSPHGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm having a problem finding out how I can split data in one field and
> then use the values to match records in another table.
> In table 1, I have a field where the values looks like
> e.g. "229 231 233 235". What I'd like to do, is to match these 4
> numbers with an ID in table 2 to get the values from table2. I.e. I'd like
> to split up this one value to 4 values (229, 232, 233,235).
> I've tried to use REPLACE to put in a "," between each so I could use it
> as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
> Has any of you any other suggestions to how it can be done? It's not
> always the same number of numbers in the field (e.g. another one could be
> "456 29580010" ). The field is a VARCHAR(1000) and there're also some
> text strings in it. These seems to be some old crab though and I don't
> need these values.
> The only "general" thing with the formatting, seems to be that there are 2
> spaces between each of the numbers I'd like to get out, so I think I can
> use that as a "delimiter".
> Anyone who has some hints to this?
> Regards
> Steen
>|||On Thu, 19 Jan 2006 13:34:07 +0100, Steen Persson (DK) wrote:

>Hi
>I'm having a problem finding out how I can split data in one field and
>then use the values to match records in another table.
>In table 1, I have a field where the values looks like
>e.g. "229 231 233 235". What I'd like to do, is to match these 4
>numbers with an ID in table 2 to get the values from table2. I.e. I'd
>like to split up this one value to 4 values (229, 232, 233,235).
>I've tried to use REPLACE to put in a "," between each so I could use it
>as "WHERE xxx IN (229,231,233,235)" but I can get the syntax right for it.
>Has any of you any other suggestions to how it can be done? It's not
>always the same number of numbers in the field (e.g. another one could
>be "456 29580010" ). The field is a VARCHAR(1000) and there're also
>some text strings in it. These seems to be some old crab though and I
>don't need these values.
>The only "general" thing with the formatting, seems to be that there are
>2 spaces between each of the numbers I'd like to get out, so I think I
>can use that as a "delimiter".
>Anyone who has some hints to this?
Hi Steen,
In addition to what others already wrote on this, I'll give you this
link:
http://www.sommarskog.se/arrays-in-sql.html
Also, try to change the design. Arrays really should not be stored in a
single column.
Hugo Kornelis, SQL Server MVP