|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help on "splitting up" data in a fieldI'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 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 Show quote "Steen Persson (DK)" <s**@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:
Show quote > Steen I must admit, that I can't really see the purpose of the script, and > 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)" <s**@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 >> >> > > Thanks Uri 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'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 >>> numbers with an ID in table 2 to get the values from table2. I.e. SELECT * FROM #Source WHERE NOT EXISTS(SELECT * FROM #Target WHERE #Source.col1=#Target.col1) Show quote "Steen Persson (DK)" <s**@REMOVEdatea.dk> wrote in message news:%233vpwsPHGHA.516@TK2MSFTNGP15.phx.gbl... > 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)" <s**@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 >>> >>> >> >> > 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 Uri Dimant wrote:
Show quote > I'm really sorry Steen , by posting Anith's example I did mean to give you Thanks for you input. I'll have to look further at the example. Right > 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 >>>> numbers with an ID in table 2 to get the values from table2. I.e. > > SELECT * FROM #Source WHERE NOT EXISTS > (SELECT * FROM #Target WHERE #Source.col1=#Target.col1) > > > > > "Steen Persson (DK)" <s**@REMOVEdatea.dk> wrote in message > news:%233vpwsPHGHA.516@TK2MSFTNGP15.phx.gbl... >> 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)" <s**@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 >>>> >>>> >>> >> 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 > > 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 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 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 Show quote "Steen Persson (DK)" <s**@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 > > Jim Underwood wrote:
Show quote > I think this might help you out, but there are problems with the aproach... The script is only for my own use, so I'm not so worried about > 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)" <s**@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 >> >> > > Hi Jim 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/dataModeling101.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/prodtechnol/sql/70/books/c0618260.mspx http://www.sql-server-performance.com/optimizing_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 Show quote "Steen Persson (DK)" <s**@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:
Show quote >Hi Hi Steen,> >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? 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 Hugo Kornelis wrote:
Show quote > On Thu, 19 Jan 2006 13:34:07 +0100, Steen Persson (DK) wrote: Thanks for your input everybody. I can see that most of the suggestions > >> 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. > is along the same route that I've already been myself. I think I'll just have to work a bit more with it to put something together that will work. With regards to the design of the table, I agree that it doesn't look like the best solution. It's not something I can do anything about though, since this is just an application we've bought like that. Since the vendor do fairly good job in other areas of the product, I assume that there are some valid reasons to why this specific field/function has been coded the way it has. Regards Steen >> Since the vendor do fairly good job in other areas of the product, I assume that there are some valid reasons to why this specific field [sic] /function has been coded the way it has. << Why do you assume that this product was one coded by ONE UNIQUEPROGRAMMER, with a single vision? Real world development often assigns task to good people and rotten ones. Systems are irregular in quality. >> With regards to the design of the table, I agree that it doesn't look like the best solution. It's not something I can do anything about << I have a friend who quit 6-digit salary job last year because the drugcompany he was consulting with had a screwed up database that could kill people. Are you in such a situiation? If so, I will be an expert witness for your when you blow the whistle on them. |
|||||||||||||||||||||||
Other interesting topics