Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

replacing numbers with strings using PATINDEX Expand / Collapse
Author
Message
Posted Friday, September 27, 2013 1:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 11, 2013 8:20 AM
Points: 10, Visits: 73
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.
Post #1499236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse