updsate query

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  January 24, 2012 at 12:19 am

  #254218

  Hi

  friendsI always required to update one column which contains path.

  eg. Myfolder/folder/dialy/20110531/sometext.

  All the structure in this column remains same except the change of the date. So anybody has idea how to update only 20110531 part of the string in the above.

  Thanks.

  Abhas

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  January 24, 2012 at 1:49 am

  #1437500

  Here is one way of doing so:

  declare @vc varchar(50)

  declare @NewDate char(8)

  --New date to replace

  select @NewDate = convert(char(8),getdate(),112)

  --Folder with date

  set @vc = 'Myfolder/folder/dialy/20110531/sometext'

  --Use the patindex fucntion in order to find the place of the date. Then the part before the date, add to it the new date and then add the part after the date

  set @vc = substring(@vc,1,patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@vc)) + @NewDate + substring(@vc,patindex('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @vc)+ len(@NewDate),len(@vc))

  select @vc

  Adi

  --------------------------------------------------------------
  To know how to ask questions and increase the chances of getting asnwers:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/