Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jan 21, 2013 02:14 PM by limno
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:
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?
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.
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()
Jan 21, 2013 02:14 PM|LINK
You can simply use REPLACE twice to UPDATE:
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.