Split Dual Delimited Strings in TSQL

Hi,
I have two table columns each containing delimited strings.  One of them is called LOCAL_PRIMARY_KEYS, and the other is called EXTERNAL_PRIMARY_KEYS.

LOCAL_PRIMARY_KEYS contains: 'Last_Name,First_Name,Birthdate'
EXTERNAL_PRIMARY_KEYS contains: 'LST_NM,FRST_NM,BDATE'

What I want to produce is a string that looks like this:

WHERE Last_Name = LST_NM AND
First_Name = FRST_NM AND
Birthdate = BDATE

How do I do this using only TSQL?  I am using SQL Server 2005.

Thanks in advance.


crickpaoloAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I would implement a function to do that, to make it generic.

first, let me introduce a modified function that I use alot:


CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30), r int )
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          , r int identity(1,1)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT INTO @result ( Value, r )
     SELECT value, r
        FROM @TempList
     RETURN
END    


and your new function could look like this:

CREATE FUNCTION dbo.BuildWhereClause ( @LOCAL_PRIMARY_KEYS varchar(max), @EXTERNAL_PRIMARY_KEYS varchar(max) )
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @res VARCHAR(MAX)
  DECLARE @l TABLE ( value varchar(100), r int )
  DECLARE @e TABLE ( value varchar(100), r int )

  INSERT INTO @l (value, r ) SELECT value, r FROM dbo.ParmsToList(@LOCAL_PRIMARY_KEYS)
  INSERT INTO @e (value, r ) SELECT value, r FROM dbo.ParmsToList(@EXTERNAL_PRIMARY_KEYS)

  SELECT @res = COALESCE(@res + ' AND ' , ' WHERE ') +  ' ' + l.value + '=' + r.value
   FROM @l l
   JOIN @r r
     ON r.r = l.r
   ORDER BY l.r
 
  RETURN @res
END




and you might use it like this:
select dbo.BuildWhereClause( LOCAL_PRIMARY_KEYS, EXTERNAL_PRIMARY_KEYS) from yourtable


note: untested code... just written here to get some ideas
0
 
Patrick MatthewsCommented:
WHERE LEFT(LOCAL_PRIMARY_KEYS, CHARINDEX(',', LOCAL_PRIMARY_KEYS) - 1) =
       LEFT(EXTERNAL_PRIMARY_KEYS, CHARINDEX(',', EXTERNAL_PRIMARY_KEYS) - 1)
AND
SUBSTRING(LOCAL_PRIMARY_KEYS, CHARINDEX(',', LOCAL_PRIMARY_KEYS) + 1, CHARINDEX(',', LOCAL_PRIMARY_KEYS, CHARINDEX(',', LOCAL_PRIMARY_KEYS) + 1) - CHARINDEX(',', LOCAL_PRIMARY_KEYS) - 1) =
      SUBSTRING(EXTERNAL_PRIMARY_KEYS, CHARINDEX(',', EXTERNAL_PRIMARY_KEYS) + 1, CHARINDEX(',', EXTERNAL_PRIMARY_KEYS, CHARINDEX(',', EXTERNAL_PRIMARY_KEYS) + 1) - CHARINDEX(',', EXTERNAL_PRIMARY_KEYS) - 1)
AND
RIGHT(LOCAL_PRIMARY_KEYS, CHARINDEX(',', REVERSE(LOCAL_PRIMARY_KEYS) - 1)) =
      RIGHT(EXTERNAL_PRIMARY_KEYS, CHARINDEX(',', REVERSE(EXTERNAL_PRIMARY_KEYS) - 1))
0
 
crickpaoloAuthor Commented:
Thanks Angellll, you are a Genius!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.