CSV Nesting and Parsing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CSV Nesting and Parsing

Author

Topic

NickRice
Starting Member

13 Posts

Posted - 2003-09-01 : 07:10:34

Please excuse me if you find the following problem a bit irritating or weird but its just a complex query I have in mind. Kindly do not suggest normalising the table(s) as I wish to resolve it as is.

Ok, here's the situation, I've got two tables:

Table1.ID - Table1.PlaceHolderText
-----------------------------------
1 - Hello [Name], Is your age [Age]?

Table2.FKID - Table2.PlaceHolderName - Table2.PlaceHolderValues
---------------------------------------------------------------
1 - Name - John,Lucy,Jack
1 - Age - 16,24,30

Both tables can have multiple records (ie: unqiue Table1.ID's). Now, I wish to retrieve multiple records with nested parsing and string manipulation (basically text replace), such that the final resultset is:

1 - 1 - Hello John, Is your age 16?
2 - 1 - Hello John, Is your age 24?
3 - 1 - Hello John, Is your age 30?
4 - 1 - Hello Lucy, Is your age 16?
5 - 1 - Hello Lucy, Is your age 24?
6 - 1 - Hello Lucy, Is your age 30?
7 - 1 - Hello Jack, Is your age 16?
8 - 1 - Hello Jack, Is your age 24?
9 - 1 - Hello Jack, Is your age 30?

Any help or ideas will be of great help! Thank you.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 08:42:24

Wow, it is too complex for me at the end of my workday.

Friendly hint: only Arnold F. can resolve this brainteaser. ;-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-09-01 : 08:47:39

I suggest normalising the table(s).

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 08:56:58

quote:
Kindly do not suggest normalising the table(s)

???

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 09:17:44

quote:
if you find the following problem a bit irritating or weird

Not at all.

NickRice
Starting Member

13 Posts

Posted - 2003-09-01 : 11:43:56

quote:
Originally posted by Arnold Fribble

I suggest normalising the table(s).

Ok. I've normalised the table as follows:

Table1.ID - Table1.PlaceHolderText
------------------------------------
1 - Hello [Name], Is your age [Age]?

Table2.FKID - Table2.PlaceHolderName - Table2.PlaceHolderValue
--------------------------------------------------------------
1 - Name - John
1 - Name - Lucy
1 - Name - Jack
1 - Age - 16
1 - Age - 24
1 - Age - 30

How can I go about the parsing procedure now? It's driving me nuts and I have a gut feeling that there is a reliable solution to it.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-01 : 12:51:50

select t1.ID,

replace(replace(PlaceHolderText,'['+x2.PlaceHolderName+']',x2.PlaceHolderValue),
'['+y2.PlaceHolderName+']',y2.PlaceHolderValue)

from t1, t2 x2, t2 y2

where t1.ID=x2.FKID and t1.ID=y2.FKID and
x2.PlaceHolderName < y2.PlaceHolderName

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-09-01 : 15:42:13

Essentially, I agree: I don't think it's possible to produce a general set-based solution without resorting to dynamically creating the SQL... well, it might be possible to do it with a recursive function (if that counts as set-based)... might get back to you on that.

Anyway, this is a bit yucky, but I think I've got all the quotes in the right places!


DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @r varchar(10)
SELECT @sql1 = 'PlaceholderText', @sql2 = '', @r = '1'

SELECT
  @sql1 = 'REPLACE(' + @sql1 + ', ' + QUOTENAME('[' + n + ']', '''') + ', R' +
          @r + '.PlaceHolderValue)',
  @sql2 = @sql2 + ' INNER JOIN T2 AS R' + @r + ' ON T1.ID = R' + @r +
          '.FKID AND R' + @r + '.PlaceHolderName = ' + QUOTENAME(n, ''''),
  @r = CAST(CAST(@r AS int) + 1 AS varchar(10))
FROM (SELECT DISTINCT PlaceHolderName AS n FROM T2) AS A

--PRINT N'SELECT T1.ID, ' + @sql1 + N' FROM T1' + @sql2 
EXEC(N'SELECT T1.ID, ' + @sql1 + N' FROM T1' + @sql2)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-09-01 : 16:25:09

This is just proof-of-concept. I'm not recommending you do this!


CREATE FUNCTION dbo.replacePlaceholders(@nprev AS varchar(100))
RETURNS @t TABLE (id int NOT NULL, placeholderText varchar(8000)
    COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)
BEGIN
  DECLARE @n varchar(100)
  SET @n = (SELECT MIN(placeholderName) FROM T2 WHERE placeholderName > @nprev)
  IF @n IS NULL
  BEGIN
    INSERT INTO @t
    SELECT ID, placeholderText
    FROM T1
  END
  ELSE
  BEGIN
    INSERT INTO @t
    SELECT ID, REPLACE(placeholderText, '[' + @n + ']', placeholderValue)
    FROM dbo.replacePlaceholders(@n) AS T1
    INNER JOIN T2 ON T1.ID = T2.FKID AND T2.placeholderName = @n
  END
  RETURN
END
GO

SELECT * FROM dbo.replacePlaceholders('')

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-02 : 09:22:38

Arnold Fribble:

Great. As ever.

NickRice
Starting Member

13 Posts

Posted - 2003-09-04 : 05:15:58

Thanks guys! It's been of a great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources