CODEWITHSUNDEEP
mysql
mweingand
mweingand

Reputation: 11

How to remove certain string of charactors in mySQL?

Let me start off be saying I am new to trasactsql and query so please be kind. I have a DB with about 1700 records for my product file, and one field (name) has the product code, a space and then the name of my product. I have another field (pcode) with just the product code that has numbers and possibly a letter or two that matches the beginning of the "name" field. I need to remove all characters in front of the space in 'name" including the space. Is there a way to remove the characters if they match what is in "pcode"?

Here is an example.

What I have;

pcode | name

1234 | 1234 widget

What I want;

pcode | name

1234 | widget

There are some names with spaces in them. ie. "Left Handle Button side" or "Control "Lever"

Thanks in advance. Michael

Upvotes: 0

Views: 70

Answers (2)

DualCore
DualCore

Reputation: 420

Try to export it to MS Excel for example and do it manually. Next time learn about 12 gold rules when creating SQL database.

http://en.m.wikipedia.org/wiki/Codd%27s_12_rules

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In a select, you can do:

select pcode, substring_index(name, ' ', -1)
. . .

In an update:

update table t
    set name = substring_index(name, ' ', -1)
    where name like '% %';

If you want to change the data in place (using an update), check the logic (or copy the table) before doing the update. This might not do what you expect, for instance, if the name itself has a space in it.

Upvotes: 1

Related Questions