Select quert with REPLACE function

• Minnu

  SSCertifiable

  Points: 6451

  More actions

  May 22, 2015 at 3:19 am

  #300945

  Hi Team

  using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.

  select id, city,

  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,

  'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),

  'JRK_Saldae', 'Bougie')))

  New_city_name

  from towns

  Pleae try.

• Phil Parkin

  SSC Guru

  Points: 246688

  More actions

  May 22, 2015 at 3:32 am

  #1799939

  Minnu (5/22/2015)

  ---

  Hi Team

  using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.

  select id, city,

  LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,

  'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),

  'JRK_Saldae', 'Bougie')))

  New_city_name

  from towns

  Pleae try.

  Try what? For what reason?

  Instead of asking us to do something for no apparent reason, please state your question.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Y.B.

  SSChampion

  Points: 11642

  More actions

  May 22, 2015 at 8:23 am

  #1800023

  Despite your description being vague I 'think' I know what you want.

  If the cities need to be known by different names then you are best to just create a table or column with the cities Alias. That way you are not having to change any code in the future.

  i.e.

  SELECT

  t.id,

  t.city,

  a.alias AS new_city_name

  FROM

  towns t

  JOIN city_alias a ON a.id = t.id

  This is assuming you still need the old city names...otherwise just update the table itself.

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• ScottPletcher

  SSC Guru

  Points: 100946

  More actions

  May 22, 2015 at 8:25 am

  #1800025

  Create a replacement values table, and use it to do the data replacement.

  CREATE TABLE dbo.data_replacements (

  column_name varchar(30) NOT NULL,

  current_value varchar(100) NULL,

  replacement_value varchar(100) NULL,

  CONSTRAINT data_replacements__CL UNIQUE CLUSTERED ( column_name, current_value )

  )

  INSERT INTO dbo.data_replacements ( column_name, current_value, replacement_value )

  SELECT 'city', 'JRK_Ikosium', 'Icosium' UNION ALL

  SELECT 'city', 'JRK_Géryville', 'El_Bayadh' UNION ALL

  SELECT 'city', 'JRK_Cirta', 'Constantine' UNION ALL

  SELECT 'city', 'JRK_Rusicade', 'Philippeville' UNION ALL

  SELECT 'city', 'JRK_Saldae', 'Bougie'

  select t.id, t.city, COALESCE(city_replacement.replacement_value, t.city) AS New_city_name

  from towns t

  outer apply (

  select top (1) dr.replacement_value

  from dbo.data_replacements dr

  where

  dr.column_name = 'city' and

  dr.current_value = t.city

  ) as city_replacement

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.