Build a Delimited List And Query Syscomments, Tricks of the Trade
-
Sometimes the basics are so great. Thanks for the reminder!
-
Comments posted to this topic are about the item Build a Delimited List And Query Syscomments, Tricks of the Trade
-
Amen to that!
My coding would be so much poorer (& slower) without these little gems.
-
Great reminder!
Now the next topic would be - If you store the delimited text in a column, what's the best way to parse the individual items back out?
-
JUst a suggestion. Instead of
Set @List = ''
select @List = @List + EmailAddress + ',' from MyTable where EmailAddress is not null
Do
select @List = IsNull(@List + ',','') + EmailAddress from MyTable where EmailAddress is not null
This will save the need to remove the last comma.
-
There are more warnings to heed than just the 8000-character limit. Read this KB article before using this technique to avoid pitfalls:
http://support.microsoft.com/default.aspx?scid=kb;en-us;287515
--
Adam Machanic
whoisactive -
--------------
Now the next topic would be - If you store the delimited text in a column, what's the best way to parse the individual items back out?
--------------
The answer for java would be use "split" to parse the string into an array. .NET has a similar (if not the same, I can't remember) function as well.
If you're working with SQL, the answer is that you DON'T parse the string, as you would never use a delimited string when going from SQL to SQL.
Signature is NULL
-
Actually, the answer would be: You wouldn't store delimited text in a column.
--
Adam Machanic
whoisactive -
A Limitation of querying syscomments:
Larger procedures can have multiple rows in syscomments, basically the text is split into blocks of fixed size. The text you are searching for could be split between two blocks, and you wouldnt find it. I think I wrote an SP that solved this once .. its not handy right now.
Only aplies to SPs >4K, and then you have to be unlucky.
-
The prepared always seem to be luckier
DECLARE @search varchar(100)
SET @search = '%sysobject%'
SELECT
OBJECT_NAME(id) AS ProcName,
SUBSTRING(text, PATINDEX(@search, text) - 100, 200) AS Context
FROM
syscomments with (nolock)
WHERE
text LIKE @search
UNION ALL
SELECT
'Overlap hit! -- ' + OBJECT_NAME(c1.id) AS ProcName,
SUBSTRING(c1.text, LEN(c1.text) - 100, 101) + SUBSTRING(c2.text, 1, 100) AS Context
FROM
syscomments c1 with (nolock)
INNER JOIN syscomments c2 with (nolock)
ON c1.id = c2.id
AND c1.colid = c2.colid - 1
AND SUBSTRING(c1.text, LEN(c1.text) - 100, 101) + SUBSTRING(c2.text, 1, 100) LIKE @search
-
In case anyone running SQL 2005 reads this thread, I want to note that there are other ways to do the delimited string thing in SQL2k5. For example, this shows tables with comma-delimited list of columns (and I'm sure many variants of this technique can be found...)
select o.name,
replace(replace(replace(
(select c.name from sys.columns c where o.object_id = c.object_id order by c.column_id for xml raw),
'"/><row name="', ','),
'<row name="', ''),
'"/>', '')
as cols_as_xml
from sys.tables o
-
Personally, I prefer using a single coalesce statement to produce a delimited list:
declare @List varchar(max)
select @list = coalesce(@list ',' + nullif(col1, ''), nullif(col1, ''), @list, '')
from dbo.Table
This will deal with null column values, as well as empty (zero-length) column values. If you want to include zero-length strings, just get rid of the nullif statements. This version also doesn't leave a trailing comma at the end. For SQL 2000, the declare has to be varchar(8000), of course.
For SQL 2000, you can also check:
select sum(len(col1))
from dbo.Table
where col1 is not null
That will tell you whether you are going to truncate or not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply