RE: DTS How to parse a varcharfield
- From: frank chang <frankchang@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 6 Jul 2006 07:10:02 -0700
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@xxxxxxxxx" wrote:
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.
- Follow-Ups:
- Re: DTS How to parse a varcharfield
- From: nathanious
- Re: DTS How to parse a varcharfield
- References:
- DTS How to parse a varcharfield
- From: nathanious
- DTS How to parse a varcharfield
- Prev by Date: Re: Transfer record sequence
- Next by Date: Re: Transfer record sequence
- Previous by thread: DTS How to parse a varcharfield
- Next by thread: Re: DTS How to parse a varcharfield
- Index(es):
Relevant Pages
|
