Looking for a way to locate a value in a database that begins with a specific character and then removing said character

Question

I have a table containing both relative and absolute urls for a news site. The author of an article has the ability to define a relative link to another page on the site, or a complete link to another site. Due to the default value being "/" in some cases he has pasted a link over the dash and confused the site as to whether the link is relative or absolute, and we end up with links like so: www.authors.site/www.authorsIntended.link.

Normaly I'd just fix this by hand, but the author can use up to 3 links per article, and since most of the affected links are the 2nd or 3rd link provided this has slid under the radar for some time.

I can pull up all of the affected links with this sql:

    SELECT * FROM `kalamazo_fields_values` WHERE `value` like "/http%"

Though I can't figure out a way to remove only the first character from a selection. I haven't been using sql for all that long so I may be overlooking a simple solution, but for the life of me I haven't been able to find a solution to this.

Possible duplicate of [MySQL string replace](https://stackoverflow.com/questions/5956993/mysql-string-replace) — Boris Sokolov, Jan 08 '19 at 17:14

score 1 · Accepted Answer · answered Jan 08 '19 at 17:13

1

you could use an update .. and substr

update  `kalamazo_fields_values` 
set  value  = substr( `value`, - (length( `value`) -1))
where `value` like "/http%

answered Jan 08 '19 at 17:13

scaisEdge

124,973
10
73
87

Looking for a way to locate a value in a database that begins with a specific character and then removing said character

1 Answers1