September 27, 2013 at 1:54 am
Hi
I am migrating data from a legacy system to a new system. I am trying to replace legacy client id with the new client ids.
create table sampleclient
(clientid int,
clientcode varchar(10))
INSERT sampleclient VALUES
(1, 'ABCD'),
(2, 'EFGH'),
(3, '3RETS'),
(4, 'T6UY');
I am trying to create a function where i can pass in a string with the old client id and return the new clientcode.
select dbo.ReplaceClientIDwithClientcode('ActiveMaster.Fields("ClientID").Value = 1')
I am expecting the output to be
ActiveMaster.Fields("ClientID").Value = 'ABCD'
Some of the input strings are bit complex.
ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775
Here is the code i have so far.
DECLARE
@clientid varchar(100),
@clientcode varchar(100),
@string varchar(3000),
@start int,
@end int,
@len int
SET @string = 'ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775'
while PATINDEX('%[0-9]%',@string) > 0
begin
set @len = len(@string)
set @start = PATINDEX('%[0-9]%',@string)
--print 'start of first number ' + cast (@start as varchar(1000))
--print substring(@string, @start, @len)
set @end = PATINDEX('%[^0-9]%',substring(@string, @start, @len))-1
--print @end
if @end = -1
begin
set @clientid = substring(@string, @start, @len)
end
else
begin
set @clientid = substring(@string, @start, @end)
end
print @clientid
set @clientcode = (select clientcode from clients where ClientID = @clientid)
print @clientcode
set @string = REPLACE(@string, @clientid ,''''+ @clientcode + '''')
--set @loopbreaker = @loopbreaker +1
--if @loopbreaker = 10
--break
end
print @string
The problem i am facing is that the client code which replaces the client id also contains numbers. My loop finds the number again and chops up the client code.
Any help is greatly appreciated.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply