Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Help getting results with comma delimited field (MS SQL 2005)

I have several tables that I need to join and I can do it, the one part that holds me up is that one of the columns contains a comma delimited ID list.  This is what my query would have looked like:

SELECT t.Iden, t.runList, t.tripDate, at.accountID, ai.emailAddress, tt.firstname, tt.lastname, r.runcode FROM tbl_trips t
LEFT JOIN tbl_runs r ON r.iden = t.runList
LEFT JOIN tbl_accountTrips at ON at.tripID = t.Iden
LEFT JOIN tbl_AccountInfo ai ON ai.iden = at.accountID
INNER JOIN tbl_tripTravelers tt on tt.tripID_fk = t.iden
WHERE LEFT(r.runCode, 2) = 'LB'
ORDER BY ai.emailaddress

My biggest criteria is that I need to filter by the r.runCode, but this is how the tables are setup.

tbl_runs (records split by the | )
-------------------
Iden (1 | 2 | 3 | 4 | 5 | etc.)
runCode (LB1034 | SR3902 | LB3203 | etc)

tbl_trips
-------------------
Iden (1 | 2 | 3 | 4 | 5 | etc.)
runList(1,3 | 4 | 2 | 4,5 | etc)     <---- Notice that some records have 2 numbers separated by a comma in the field

The only link to tbl_runs.runcode is by (tbl_trips.runList to tbl_runs.iden)

In order for my query to work I need to add the following:  t.runList NOT LIKE ('%,%'), but I need to know even those with comma delimited values.

I have searched all over the Internet and I can't seem to find a simple solution.  Does it not exist?  Because when I run the query, I get some conversion error from varchar to int.

Somehow I need to check both the 1 and the 3 from the record.  Can I create two records in a temp table or something?

I am coding with SQL 2005 and using Coldfusion 8, in case anyone needs to know.
0
perkley
Asked:
perkley
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you need my function dbo.ParmsToList(), and use cross apply somewhere in your code.
as I am about to leave for bed, I don't see clear enough to write the code...

if you have trouble (and if nobody else posted until tomorrow), I might help more specifically...
0
 
_agx_Commented:
(No points please)

I too am going to bed, but in the mean time I think the function angelIII wrote is here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21644852.html

(Though obviously the ideal situation would be not to store a comma delimited list of values, if you have any choice about it)
0
 
perkleyAuthor Commented:
I think I am going to need help, I don't understand cross apply and I haven't yet got anything to work.  I have created the UDF.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need something like this:
SELECT t.Iden, t.runList, t.tripDate, at.accountID, ai.emailAddress, tt.firstname, tt.lastname, r.runcode 
FROM tbl_trips t
CROSS APPLY dbo.ParmsToList(t.runList, ',') l
LEFT JOIN tbl_runs r ON r.iden = l.Value
LEFT JOIN tbl_accountTrips at ON at.tripID = t.Iden
LEFT JOIN tbl_AccountInfo ai ON ai.iden = at.accountID
INNER JOIN tbl_tripTravelers tt on tt.tripID_fk = t.iden
WHERE LEFT(r.runCode, 2) = 'LB'
ORDER BY ai.emailaddress

Open in new window

0
 
perkleyAuthor Commented:
I am researching on the Internet, but I just keep getting "Incorrect syntax near ','."  I don't see anything wrong, but I am still looking.
0
 
perkleyAuthor Commented:
Any ideas?  I can't seem to get past this.  If I do the query just like this:

SELECT t.Iden FROM tbl_trips t
CROSS APPLY dbo.ParmsToList(t.runList, ',') l
LEFT JOIN tbl_runs r ON r.iden = l.Value

I still get the error: "Incorrect syntax near '.'  Is it in the ParmsToList function.  How do I trace something like this down?  I looked through the ParmsTolist and see no period.
0
 
perkleyAuthor Commented:
Ok, just so people know what the problem was, my database we set to be 8.0 compatibility level, and I just had to change it to 9.0 and it worked fine.

I found the solution from this link: http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic9819.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you take the version with 2 parameters of ParmsToList() or with 1 parameter?
0
 
perkleyAuthor Commented:
I used the one with 2 parameters
create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, is see you meanwhile found the problem.
glad I could help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now