Help with query

[nadeemshafi9]

i have a table called zone table it has a feild called zone_type and a feild called zone_brand

 

the zone type feild currently has data suck as ,1, or it can be two types ,1,2,

 

the zone brand feild has a 3 letter abbreviation only one of 'htn' or 'ham' or somthing.

 

i need to execute SQL to change all the zone_type's with ,1ham, or ,1htn,  or ,1htn,2htn, etc...

 

THE PROBLEM

 

some zone_type's have more than one type eg. ,1,2,

 

so

 

teh furthest i got is updating zones with only one zone_type 

 

UPDATE zone_table

SET zone_type = ',htn1,'

WHERE zone_brand = 'htn' AND zone_type = ',1,';

 

UPDATE zone_table

SET zone_type = ',htn2,'

WHERE zone_brand = 'htn' AND zone_type = ',2,'

 

UPDATE zone_table

SET zone_type = ',hgi1,'

WHERE zone_brand = 'hgi' AND zone_type = ',1,'

 

UPDATE zone_table

SET zone_type = ',hgi2,'

WHERE zone_brand = 'hgi' AND zone_type = ',2,'

 

UPDATE zone_table

SET zone_type = ',hgi3,'

WHERE zone_brand = 'hgi' AND zone_type = ',3,'

 

UPDATE zone_table

SET zone_type = ',crd1,'

WHERE zone_brand = 'crd' AND zone_type = ',1,'

 

UPDATE zone_table

SET zone_type = ',dbt1,'

WHERE zone_brand = 'dbt' AND zone_type = ',1,'

 

UPDATE zone_table

SET zone_type = ',ham1,'

WHERE zone_brand = 'ham' AND zone_type = ',1,'

 

UPDATE zone_table

SET zone_type = ',wac1,'

WHERE zone_brand = 'wac' AND zone_type = ',1,'

 

AS I SAID

 

some of them have multiple types

 

i know that the DB is not normalised properly

 

can i replace parts of the feild instead of teh whole feild ?

[fenway]

the zone type feild currently has data suck as ,1, or it can be two types ,1,2,

That's the real problem.  You can get fancy and figure out string-y ways do this... but it's much better to normalize your tables.

[nadeemshafi9]

the zone type feild currently has data suck as ,1, or it can be two types ,1,2,

That's the real problem.  You can get fancy and figure out string-y ways do this... but it's much better to normalize your tables.

 

i executed multiple queries and i done it, i am no way trying to defend the un normality of this, its not my work

[fenway]

Not your work?

 

REPLACE() can find certain parts of strings.

[nadeemshafi9]

Not your work?

 

REPLACE() can find certain parts of strings.

 

legacy sys

[fenway]

Well, REPLACE will still work...