update varchar fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

update varchar fields

Author

Topic

cubetto
Starting Member

4 Posts

Posted - 2005-07-28 : 10:28:28

i have some fields in my database wich where uncorrectly inserted with this format "VALUE/"
i.e. '"Peter/"', '"George/"'

if i run

SELECT *
FROM `lm2_users`
WHERE lastname LIKE '\"%\\\"'

i can select all of them

Is there an update command that i can use to update this field into a substring of the previous value? or should i use php for this task?

i want to select all lastname LIKE '\"%\\\"' and update them with just the % part of it.
Hope i make my self clear enough

thanks
Nicolas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-28 : 10:40:39

Do you want to replace VALUE/ as VALUE?

Madhivanan

Failing to plan is Planning to fail

cubetto
Starting Member

4 Posts

Posted - 2005-07-28 : 10:43:45

no
i want to replace "VALUE/" as VALUE

Nicolas

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 10:44:05

If you just want to remove some rogue characters anywhere within the column you could do:


UPDATE U
SET lastname = REPLACE(REPLACE(lastname, '"', ''), '/', '')
FROM lm2_users U
WHERE    lastname LIKE '%"%'
      OR lastname LIKE '%/%'

if the pattern is restricted to surrounding the data you could probably do


UPDATE U
SET lastname = SUBSTRING(lastname, 2, LEN(lastname)-3)
FROM lm2_users U
WHERE    lastname LIKE '"%/"'

if I've got the "-3" bit calculated correctly

I'm sure there is a good reason why you hav a single quote around your table name, and the backslashes in your LIKE statement escapping characters, but they won't work in regular SQL Server AFAIK

Kristen

cubetto
Starting Member

4 Posts

Posted - 2005-07-28 : 10:58:24

I cannot get your SQL to run in phpMyAdmin 2.6.2-pl1 with MySQL 4.0.20

my WHERE lastname LIKE '\"%\\\"' statement works though

Nicolas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-28 : 11:02:06

This is SQL Server Forum
Make sure MySQL has Replace or its equivalent function

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 11:03:23

Ah ... sorry, wasn't expecting you were running it in something non-SQL-Server.

If you run it in Query Analyser you won't have to escape the escape characters! If you can't use Q.A. then you'll no doubt have to do surgery on all quotes and slashes

Kristen

cubetto
Starting Member

4 Posts

Posted - 2005-07-28 : 12:20:47

sorry for the confussion.

im such a newbie

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 12:29:11

Ah ... didn't even spot the MySQL reference, thanks Mad.

Query Analyser aint going to be much use then.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources