May 22, 2015 at 3:19 am
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.
May 22, 2015 at 3:32 am
Minnu (5/22/2015)
Hi Teamusing 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 22, 2015 at 8:23 am
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.
May 22, 2015 at 8:25 am
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".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply