Home > IT Answers > Development > sql update a column to remove a certain chara...
Lazy167
55 pts.
0
Q:
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
ASKED: May 26 2009  1:41 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29340 pts.
0
A:
 RATE THIS ANSWER
+2
Click to Vote:
  •   2
  •  0
  • AddThis Social Bookmark Button
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:

 UPDATE book SET your_column = REPLACE(your_column,'*','');
Last Answered: May 26 2009  2:28 PM GMT by Carlosdl   29340 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Lazy167   55 pts.  |   May 26 2009  2:49PM GMT

thx 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

 

Carlosdl   29340 pts.  |   May 26 2009  6:42PM GMT

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.

 

JackX   10 pts.  |   May 27 2009  6:14AM GMT

That is so interesting..Well aside from that trivia, did you know that The Credit Card Bill has been both lauded and lamented, and it’s stirring up some controversy. Critics of the Senate credit card bill, AKA the Credit Card Bill of Rights, claim it is a move towards socialist tendencies, but more disturbing even that that is that the people who use credit cards responsibly, the ones that timely pay their bills, are the ones that will have higher fees and interest rates. The thought of responsible users of credit thinking about fast loans to pay off credit card interest rather than the less responsible is certainly not something anyone would relish. President Obama has endorsed the bill, but some people would give short term loans to get rid of the <a rev="vote for” title="Pay Your Credit Cards On Time? You Will Be Penalized!” href="http://personalmoneystore.com/moneyblog/2009/05/19/senate-credit-card-bill/”> Credit Card Bill</a>.

 
0