Removing text and brackets

General

Removing text and brackets

update samples.cities1

from samples.cities

set city_name=

 case

when position('(' in city_name)<>0

then

substring(city_name,-1,index(city_name,'(')) else city_name

end

 where city_id=105100

or

update samples.cities

from (select

case

when position('(' in city_name)<>0

then

substring(city_name,-1,index(city_name,'(')) else city_name

end as city_name

 from samples.cities) A

 set city_name=A.city_name

 where city_id=105100

hi i am trying to run the above query but it throwing error as 3706 syntax error expected something between word 'city_name and ','

1 REPLY
Enthusiast

Re: Removing text and brackets

The SUBSTRING function has pecular argument syntax.  This is mandated by the ANSI/ISO SQL standard.  The SUBSTR function, on the other hand, works just like SUBSTRING, but it has the more conventional positional argument syntax. Your query should work if you just change the name of the function to SUBSTR.