SQL update a column to remove a certain character

Lazy167

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

Asked: May 26, 2009 1:41 PM Last updated: January 29, 2018 2:13 PM

Answer Wiki

Last updated: April 19, 2013 8:32 PM GMT

Michael Tidmarsh62,135 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,135 pts.
- carlosdl84,495 pts.

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: 4 Replies

Lazy167 May 26, 2009 2:49 PM GMT

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 May 26, 2009 6:42 PM 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.

84,495 pointsBadges:

report
priya333 Jan 29, 2018 6:42 AM GMT

Emp123456- this id how to remove EMP string in mvc.

10 pointsBadges:

report
ToddN2000 Jan 29, 2018 2:13 PM GMT

@priya333: the same way as above just replace the * with EMP UPDATE book SET your_column = REPLACE(your_column,'EMP','')

99,840 pointsBadges:

report