May 20, 2008 at 8:14 am
I have several columns in a SQL Server 2000 table. One of the column names is UPDATES. It is a concatenation of all of the columns within the table. The concatenations for UPDATES are separated by a $ sign.
I am having a problem pulling out the concatenated fields from UPDATES.
For example...If I wanted to see who updated our documentation last and if they changed the restart instructions, I cannot pull out email and restart from within UPDATES concatenation.
I have attached my query code and screen prints.
Many Thanks!
Dawn
May 20, 2008 at 8:51 am
Just to get you started...
Try using any of the string functions built in to SQL Server
charindex( 'string to find', column name )
substring( column name, start pos, end position)
These can be put in the WHERE clause along with your criteria as in
WHERE charindex( 'email', UPDATES ) > 0
May 20, 2008 at 8:51 am
Please post the code and errors or results here. I don't really want to download and open a .doc from the forums.
You can use CHARINDEX/PATINDEX to find the $ signs. You might need to do this multiple times to find the proper one based on the position in the field.
May 20, 2008 at 9:08 am
Here is the code that is in Dreamweaver.
Having trouble writing the query that will pull email and restart (for example) out UPDATES table since it contains all of the concatenations.
UPDATE #Tbl#
SET
Jobname='#Jobname#',
Email=’#Email#’,
Description='#Description#',
System='#System#',
ApplicationName='#ApplicationName#',
Restart='#Restart#',
Notification='#Notification#',
Status='#Status#',
SpecialInstructions='#SpecialInstructions#',
TrackNo='#TrackNo#',
Updated='#Updated#'
WHERE ID= #RefNo#
select updates as prevupdatehistory FROM #Tbl#
WHERE ID= #RefNo#
UPDATE #Tbl#
SET
updates='#Updated#'+
'$'+
'#Jobname#'+
'$'+
'#email#'+
'$'+
'#Description#'+
'$'+
'#System#'+
'$'+
'#ApplicationName#'+
'$'+
'#Restart#'+
'$'+
'#Notification#'+
'$'+
'#Status#'+
'$'+
'#SpecialInstructions#'+
'$'+
'#TrackNo#'+
'$$'+
'#getUpdateHistory.prevupdatehistory#'
WHERE ID= #RefNo#
May 20, 2008 at 2:56 pm
If you don't have a Numbers table, now's a good time to build one (it's easy). It will help with this problem.
This is a standard "string parsing" problem. Assuming this data is in a table, here's a possible solution:
select
substring(updates+'$', number,
charindex('$', updates+'$', number) - number),
number as Row
from dbo.Table
inner join dbo.numbers
on number <= len(updates)
and substring('$' + updates, number, 1) = '$'
where Table.ID = @InputParam_in
order by number
(You'll have to replace "dbo.Table" with your actual table name, of course.)
If you don't have a Numbers table, here's one way to create one:
create table dbo.Numbers (
Number int identity (0,1) primary key,
Junk bit)
go
insert into dbo.Numbers (Junk)
select top 10001 null
from syscolumns s1
cross join syscolumns s2
go
alter table dbo.Numbers
drop column Junk
(I haven't used SQL 2000 in a while, so I could be mistaken about the "syscolumns" part. What you want is some table or tables that will have 10k or more rows, either by itself or when cross joined.)
- 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
May 20, 2008 at 6:16 pm
GSquared (5/20/2008)
If you don't have a Numbers table, now's a good time to build one (it's easy). It will help with this problem.
... and here's how to build/use one...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply