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
1

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: 10  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.
  • 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.
    85,470 pointsBadges:
    report
  • priya333
    Emp123456- this id how to remove EMP string in mvc.
    10 pointsBadges:
    report
  • ToddN2000
    @priya333: the same way as above just replace the * with EMP UPDATE book SET your_column = REPLACE(your_column,'EMP','')
    135,495 pointsBadges:
    report
  • snalis
    I have 00000123 but I need 4-digits from right side as well as some time in 5-digits. How is this possible?
    10 pointsBadges:
    report
  • TheRealRaven
    @snalis: Please describe exactly how "some time" is determined. That will likely modify any solution. Also, what do you mean by "need 4-digits"? For what? Will there be numeric/algebraic processing? Or is it just a text substring? Also, what is the data type of the original value?
    36,420 pointsBadges:
    report
  • ToddN2000
    @snails: for you sometimes condition, wrap the logic up in a CASE. If you need help with the code, post what you have written and what the conditions for the 4 or 5 are.
    135,495 pointsBadges:
    report
  • pankaj11
    i have a column having 10,20,34,56,data...i want to delete only 34...how is it possible?
    10 pointsBadges:
    report
  • ToddN2000
    @pankaj11: with the DELETE command.
    DELETE from mytablename
    where mycolumnname = 34
    135,495 pointsBadges:
    report
  • TheRealRaven
    @pankaj11 : Please show the result that you want. Previous responses already show how you'd remove the "34" characters withe the REPLACE() function. Also, do you want to remove "34" from every row that has those characters? Or is there just one row that will be affected? Do you only want "34" removed when it appears in those positions?
    36,420 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.

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

Following

Share this item with your network: