Update Row Wildcard Possible?

• Alex 13965

  SSC Rookie

  Points: 34

  More actions

  May 4, 2017 at 1:27 am

  #321204

  Hi,

  We have a load of rows with file paths, now we are just wondering is it possible to amend this slightly i.e.

  Current Folder Locations

  C:\ABC\DEF\BANK
  C:\ABC\DEF\ACCOUNTS

  Can we amend just taking a folder out

  C:\ABC\BANK
  C:\ABC\ACCOUNTS

  Is this possible with SQL script?

• Thom A

  SSC Guru

  Points: 99143

  More actions

  May 4, 2017 at 2:03 am

  #1941049

  Something like this?
  CREATE TABLE #FilePaths (FilePath varchar(500));
GO

  INSERT INTO #FilePaths
VALUES
  ('C:\ABC\DEF\BANK'),
  ('C:\ABC\DEF\ACCOUNTS'),
  ('C:\DEF\ABC\EXPENSE'),
  ('C:\ZYX\WVU\INVOICES');
GO

  SELECT *
FROM #FilePaths;
GO

  UPDATE FP
SET FilePath = STUFF(FP.FilePath, D2.CI, D3.CI-D2.CI,'')
FROM #FilePaths FP
    CROSS APPLY (VALUES (CHARINDEX('\',FP.FilePath))) D1(CI)
    CROSS APPLY (VALUES (CHARINDEX('\',FP.FilePath,D1.CI +1))) D2(CI)
    CROSS APPLY (VALUES (CHARINDEX('\',FP.FilePath,D2.CI +1))) D3(CI);
GO

  SELECT *
FROM #FilePaths;

  GO
DROP TABLE #FilePaths;
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  May 5, 2017 at 5:26 am

  #1941239

  Quick suggestion, use the REPLACE function
  😎
  USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @FOLDER_TO_REPLACE VARCHAR(50) = '\DEF\';

  ;WITH FilePaths(FilePath) AS
( SELECT FilePath FROM
  (VALUES
('C:\ABC\DEF\BANK'),
('C:\ABC\DEF\ACCOUNTS'),
('C:\DEF\ABC\EXPENSE'),
('C:\ZYX\WVU\INVOICES')
) X(FilePath)
)

  SELECT
 REPLACE(FP.FilePath,@FOLDER_TO_REPLACE,'\') AS NEW_PATH
FROM FilePaths FP;

  Output
  NEW_PATH
--------------------
C:\ABC\BANK
C:\ABC\ACCOUNTS
C:\ABC\EXPENSE
C:\ZYX\WVU\INVOICES


  Edit: Correction