December 13, 2001 at 11:39 pm
I have about 100 variables in 6 different tables. They are unique in the sense that they all end with patt suffix. their value could be XXXXXX where number of X's depend on a value in another column. so if nmon is 123 patt would be XXXOOO , if 14 then XOOXOO etc.
how would i write something to do it wihout having to write one update for each patt variable? ideas is to
Find variable
Find Table
Update variable based on the value of another column
Find next variable and loop
Thanks for any input.
nmon(CHAR 6)----> patt(CHAR 6)
123456 XXXXXX
156 XOOOXX
35 00X0X0
6 00000X
December 18, 2001 at 5:03 pm
I'm not sure if I understand what you want to do but maybe this could help you :
CREATE TABLE #temp (nmon INT,patt VARCHAR(10))
INSERT #temp VALUES (123456,'000000')
INSERT #temp VALUES (156,'000000')
INSERT #temp VALUES (35,'000000')
INSERT #temp VALUES (6,'000000')
DECLARE @a INT
SET @a = 1
WHILE @a < 11
BEGIN
UPDATE #temp
SET patt = STUFF(patt,0+@A,1,'X')
WHERE CHARINDEX(CONVERT(VARCHAR,@A),nmon)>0
END
SELECT * FROM #temp
DROP TABLE #temp
I think if you consider every position in the "patt" column you could be succefull in donig an update for all tuples in the table.
Sica
Edited by - sica on 12/18/2001 5:03:27 PM
December 18, 2001 at 5:15 pm
Not sure I understand your example, but there is a function called patindex() that matches patterns with and without wildcards
December 18, 2001 at 5:47 pm
I thought that if patt is 6 character long (it could be max from 0 to 10) you could update every character in the patt variables at the same time.
In the example I was tring to suggest another way to look at the problem...Maybe my english is a litle bit rusty and I didn't understand the question.
December 21, 2001 at 10:43 am
Here is I am at for now
DECLARE patt CURSOR
FOR
select distinct column_name, table_name from information_schema.columns
where column_name like '%patt' and table_name like 'drugs%'
OPEN patt
DECLARE @tablename as varchar(15)
DECLARE @Patt as varchar(15)
DECLARE @STR as varchar(255)
FETCH NEXT FROM patt INTO @Patt,@tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
select @STR= 'select patkey,questdat,' + @patt + ' from ' + @tablename + ' where ' + @patt + ' is not null'
exec (@str)
----This would return about 1000 rows of data for each variable @pat (100 of em)
---- i need to go through each of these records and realign it and update the very record
---- do i need another cursor here or .........?
END
FETCH NEXT FROM patt INTO @patt,@tablename
END
CLOSE patt
DEALLOCATE patt
December 8, 2002 at 6:55 pm
This is an ideal situation where a user-defined function would suit your need.
Create one that accepts the int value and returns your 6-character string as output. I'd use the MODULO operator to determine if each place value was set. If you have things like 31 instead of 13, then change your input to character from int.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply