-2

I have a column called' memo_line_2',the value format is like :'$3000.00 (card limit increase)',how can I only extract numeric value from the column?Thanks example:

'$3000.00 (card limit increase)' -> 3000
'$5000.00 (card limit increase)' -> 5000
'$12000.00 (card limit increase)' ->12000
teresa xu
  • 1
  • So is the pattern always going to be $ then number then space - so you want to extract everything after the dollar sign and before the first space (after the dollar sign)? – NickW Jul 29 '21 at 13:30
  • Welcome! Please edit your question to show what you've done, where you're stuck, etc. Otherwise, as written, this question is unfortunately off-topic. – David Makogon Jul 29 '21 at 13:50

3 Answers3

0

You could use REGEXP_SUBSTR() for this:

SELECT REGEXP_SUBSTR(tmp.`value`, '[0-9]+') as `new_value`
  FROM (SELECT '$3000.00' as `value` UNION ALL 
        SELECT '$5000.00' as `value` UNION ALL 
        SELECT '$12000.00' as `value`) tmp

Returns:

new_value
---------
3000
5000
12000

If you would like to keep everything after the decimal, use '[0-9.]+' as your regular expression filter.

matigo
  • 1,321
  • 1
  • 6
  • 16
  • I fail to see how that's my problem. The question was about string manipulation. The solution manipulates the string as requested. If the person later experiences performance issues because they are transforming a billion rows or — worse — using `REGEXP_SUBSTR()` in the `WHERE` clause, they can ask another question – matigo Jul 29 '21 at 13:55
0

You can use:

select regexp_substr(col, '[0-9]+[.]?[0-9]*')

This will extract the digits with the cents. You can then convert to an integer or numeric:

select cast(regexp_substr(col, '[0-9]+[.]?[0-9]*') as unsigned)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

It can be done by making a custom function in your sql query

Take a look at:

https://stackoverflow.com/a/37269038/16536522

AmirHossein Moloudi
  • 1
  • 3
  • Please don't post link-only answers. Also, you're linking to another Stack Overflow post. This is not an answer. When you have enough rep, you can post this as a comment. But also note that the OP showed no work: no query, no output, no errors. And the OP needs to provide this minimum information. Otherwise, it's just a request for others to solve their problem. – David Makogon Jul 29 '21 at 13:49