SQL update a column to remove a certain character

55 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Lazy167
    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
    55 pointsBadges:
    report
  • carlosdl
    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.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following