Hello all,

I have a problem that I am not sure how to go about solving. I know
I could do this fully in code but I want to do this in a DTS Package I
wrote.

This is the scenario. I have a table that has a varchar field called
AdditionalSQLOrParameters.
In the field some of the records are Where clauses and they use an id
number in there. Below I will give you the format and example

ProcessID (PK) [int]
Name [Varchar ]
AppID (FK) [int]
AdditionalSQLOrParameters [varchar]

now in the AdditionalSQLOrParameters the user has where clauses such as
"Where ProcessID =1 or ProcessID =3"

The issue is I have this table copy over to the new database but that
field will remain the same and I need to update it. If it was a field
on its own such as AppID is that would be no problem to get the new id.
Nor would it be a problem if I could figure out how to get to the id
in the varchar field. I was wondering how do I go abot getting into
that field (Parse it) so I can get the ID and then update it. I know
that it would be simpler for me to have the user use a different where
clause, where none of the id's are hard coded but that isn't the
situation and there are a lot of records so figuring out a method to
parse and update the field would be in my best interest.

any suggestions are gladly welcomed. I fairly green when it comes to
writting DTS packages and the rest of the package I have written has
been fairly straight forward. If anyone knows a good site to reference
or a book for DTS let me know please.

Thanks in advance for the help.

Nathanious, Nick Barclay created a UDF function that parses a varchar field.
It returns the position of the character AFTER the nth(i.e. @position)
-- instance of @string in @searchfor. So you could search for the positions
of the character after the first equals sign and second equals sign . Once
you know the positions you could update the varchar string by substituting
the appropriate number.
Here is the function:

if object_id('dbo.FNInString') is not null
begin
drop function dbo.FNInString
PRINT 'Dropped function dbo.FNInString'
end
else
PRINT 'Could not drop function dbo.FNInString'
go


----------------------------------------------------------------------------------
-- FUNCTION: FNInString
--
-- PURPOSE: Returns the position of the character AFTER the nth(i.e.
@position)
-- instance of @string in @searchfor
--

--
-- SAMPLE CALL:
-- SELECT @StringPosition = dbo.FNInString(RPT.DT_STAMP, '/', 2)
--
-- MODIFICATION HISTORY:
-- DATE MOD BY REASON
-- ----------- ----------------------
----------------------------------------
-- 2006-06-01 Converted from Proc,InString,
written
-- by Nick Barclay at
--
http://www.novicksoftware.com/UDFofWeek/
--
Vol2/T-SQL-UDF-Vol-2-Num-49-instring.htm
--------------------------------------------------------------------------------------

CREATE function FNInString(
@string varchar(200),
@searchfor varchar(50),
@position int
) returns int

/*+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++
Returns the position of the character AFTER the nth instance
of the string

-- TEST CASE #1
-- should return 18
select dbo.InString('123456 123456 123456 123456', '23', 3)
as [Test Case #1]

-- TEST CASE #2
-- should return 25
select dbo.InString('test1/test2/test3/test4/test5/', '/', 4)
as [Test Case #2]

-- TEST CASE #3
declare @teststring varchar(50)
set @teststring = 'test1/test2/test3/test4/test5/'
select substring(@teststring, dbo.Instring(@teststring,'/',3),5)
as [Test Case #3]
-- should return 'test4'

-- TEST CASE #4 (variable length delimited fields
declare @teststring2 varchar(50)
set @teststring2 = 'test123/test/testtestestest/testxyz/test/'
select substring( @teststring2,
dbo.Instring(@teststring2, '/', 3),
(dbo.Instring(@teststring2, '/', 4) -1)
- dbo.Instring(@teststring2, '/', 3)
) as [Test Case #4]
-- should return 'testxyz'
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++*/

as
begin
declare @lenstring int, @poscount int, @stringpos int
set @lenstring = datalength(@searchfor)
set @poscount = 1
set @stringpos = 1
while @poscount <= @position and @stringpos <= len(@string)
begin
-- if we find the string segment we're looking for
if substring(@string, @stringpos, @lenstring)=@searchfor
begin
-- is the instance of the string the one we are
-- looking for?
if @poscount = @position
begin
set @stringpos = @stringpos + @lenstring
return @stringpos
end
-- else look for the next instance of the string
-- segment
else
begin
set @poscount = @poscount + 1
end
end
set @stringpos = @stringpos + 1
end
return null
end
go
IF OBJECT_ID('dbo.FNInString') IS NOT NULL
PRINT '<<< CREATED FUNCTION dbo.FNInString >>>'
ELSE
PRINT '<<< FAILED CREATING FUNCTION dbo.FNInString >>>'
go


"nathanious (AT) gmail (DOT) com" wrote:

Thanks. I will try that out.

I am now in the process of trying to convince the users not to use hard
coded numbers and use select statements in place to make it more
generic and robust.
Seeing that they are writting a where clause for an SQL statement they
should be able to use a SELECT statement and not just a number.


frank chang wrote:

Nathanious, This statement uses STUFF with a select statement where the 7 was
the value returned from the parsing function UDF dbo.FNInString. Similarly,
the 1 does not have to hard coded either.

SELECT STUFF('wabbit_season', 7, 1, (select dt_date from warehouse..dates
where date_nm = 'processing date'))

Result: wabbitJul 6 2006 12:00AMseason

Thank you.

"nathanious (AT) gmail (DOT) com" wrote: