replacing numbers with strings using PATINDEX

• badamaams

  SSC Veteran

  Points: 226

  More actions

  September 27, 2013 at 1:54 am

  #277583

  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.