removing unwanted text
-
Hi, I have a table that contains names of users. The problem is that some of these users have entered their names with a prefix of 'ext-' this means that some names are like 'ext-john doe' or 'ext-jane doe'. How can I possibly remove all the 'ext-' from my table and leave the 'john doe' or 'jane doe'
Can someone plaese tell me how I can remove all occurences of 'ext-' from my table
Thanks for your help
omoge
-
Hi,
You can do this as follows:
update my_table
set user_name = replace(user_name,'ext-','')
This will replace all occurrences of the string 'ext-' with the blank string ''. You have to be sure that 'ext-' is not a valid string in any other case. Which could be possible if someone had a double-barrelled surname. e.g. 'John Context-Jones'.
Unusual example, I know, but I've seen some unusual names before.
Anyway, just be aware of this - and backup your data before you make the change.
Hope that helps,
-
You could limit the update to those that have ext- as the prefix and use substring to get the rest of the text... This should overcome the John Context-Jones example (love the example!)
update my_table
set user_name = substring(user_name, 5, len(user_name) - 4)
where user_name like 'ext-%'
Good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply