Select quert with REPLACE function

Question

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.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

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.

Y.B. SSChampion Points: 11652 More actions · Answer 2

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: 101200 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".