I have many rows that have numbers after it. Say the two numbers are either 2 or 3. So for example, it would look like this:
Column1
Row 2
Row 3
Row 3
Row 2
I want to delete the numbers from these rows. All the rows names are unique so I can't put each individual row name in the update query. I'm thinking a sub-query of some kind. Any ideas?
CREATE TABLE test5( test_id INT, col varchar(100))
INSERT INTO test5 VALUES (1,'aaa2'),(2,'ddd3'),(3,'aara2'),(4,'ddtd3')
UPDATE test5 SET col=REPLACE(REPLACE(COL,'2',''),'3','')
SELECT * FROM test5
DROP TABLE test5
You can use a function to remove numbers from the mixed column for a generic solution.
bo0ga
Member
16 Points
104 Posts
How to update database to delete certain words from the rows
Jan 19, 2013 05:42 AM|LINK
I have many rows that have numbers after it. Say the two numbers are either 2 or 3. So for example, it would look like this:
Column1
Row 2
Row 3
Row 3
Row 2
I want to delete the numbers from these rows. All the rows names are unique so I can't put each individual row name in the update query. I'm thinking a sub-query of some kind. Any ideas?
I want
Shivarajb
Member
75 Points
43 Posts
Re: How to update database to delete certain words from the rows
Jan 19, 2013 10:04 AM|LINK
Use another column as Id That should be Auto Increment, you can consider Id as Primary key and you can update any row.
BestRegards.
Shivaraj B
TabAlleman
All-Star
15571 Points
2700 Posts
Re: How to update database to delete certain words from the rows
Jan 21, 2013 02:13 PM|LINK
If it is always a 1-digit number at the end, you can simply remove the last character using SUBSTRING()
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: How to update database to delete certain words from the rows
Jan 21, 2013 02:14 PM|LINK
You can simply use REPLACE twice to UPDATE:
You can use a function to remove numbers from the mixed column for a generic solution.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm