Home > IT Answers > Development > SQL update a column to remove a certain character
Lazy167
55 pts.
 SQL update a column to remove a certain character
SQL, SQL tables
I have a table (book) where there is a column that contains number in some rows and numbers with an addition *infront. I want to be able to perform some calculation with the table but the '*' is preventing me from doing this. So how do I update or alter the table so that I can remove the * and keep the numbers? *99 *12 23 *68 *48 89 13

Software/Hardware used:
ASKED: May 26, 2009  1:41 PM
UPDATED: April 19, 2013  8:32 PM
RELATED QUESTIONS
Answer Wiki:
If you are sure that the only non-numeric character that appears in the column is the asterisk, you could use the REPLACE function this way: 
<pre>
UPDATE book SET your_column = REPLACE(your_column,'*','');</pre>
Last Wiki Answer Submitted:  April 19, 2013  8:32 pm  by  Michael Tidmarsh   14,000 pts.
All Answer Wiki Contributors:  Michael Tidmarsh   14,000 pts. , carlosdl   63,580 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks works great!
On another note, is there a way to split the column into 2 so that 1 column shows whether that number has a * prefix or not?
eg
* |99
* |12
null |23
* | 68
* | 48
null | 89
null | 13

Lazy167
 55 pts.
 

Assuming that you are using SQL Server, you could use the SUBSTRING and CHARINDEX functions.

Something like this:

SELECT SUBSTRING(your_column,CHARINDEX('*',your_column),1) has_asterisk,
SUBSTRING(your_column,CHARINDEX('*',your_column)+1,LEN(your_column)) number FROM book;

If you are using Oracle, you should use SUBSTR and INSTR instead.

carlosdl
 63,580 pts.