CODEWITHSUNDEEP
sql
Tan
Tan

Reputation: 3

Removing prefix in numbers in SQL

I am trying to find maximum of a price column in SQL but the prices have a $ sign in front of them, so when I do Max(column_name) it does not show me the right answer. How do I get rid of the $ sign? Thanks.

Upvotes: 0

Views: 243

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Assuming that the amounts are all formatted the same (that is, with or without cents), then you can do:

select price
from t
order by len(price) desc, price desc
fetch first 1 row only;

Note that some databases spell len() as length(). And some spell fetch first 1 row only as limit -- or perhaps use another method such as top or rownum.

Upvotes: 1

pedrodj46
pedrodj46

Reputation: 161

A rapidly alternative is this:

SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1

Upvotes: 0

akhil vangala
akhil vangala

Reputation: 1053

If the $ is the only non alpha numeric character try something like this 

select MAX(convert(decimal(10,2),replace(price,'$','')))

Upvotes: 0

Fuzzy
Fuzzy

Reputation: 3810

assuming you are using sql-server and your data is in a varchar or nvarchar column:

DECLARE @Data varchar(max) = '$565'

SELECT MAX(CAST(REPLACE(@data,'$','') AS INT))

result:

enter image description here

So what you can do is:

SELECT MAX(CAST(REPLACE(column_name,'$','') AS INT)

Upvotes: 0

Related Questions