1
$\begingroup$

I have a field in a Redshift table that has user-generated text. The field is where users can say how much they think something costs.

Ideally it'd just be a decimal, but it's varchar. So users can type "I think this is worth \$25", or "I'd pay 55" or "\$117".

So I'm trying to use regexp_substr to pull this out. Specifically regexp_substr(f.comment_text, '\\$?[0-9]*'). But this doesn't work on a subset of entries for some reasons (eg Could do for $115).

If I remove the ? it works on that, but no longer on entries that don't use $. Why? And what should I use instead?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

I am getting that you want extract numbers with either '$' or even not without a dollar.

Why are not using just [0-9]+ for that field? The results will be like

    some $1 something >> ['1']
    some other 115 >> ['115']
    $115 some thing >> ['115']

If you want to include '\$' sign, then '\$' can be repeated zero or once or by typo more, then regex would be 

    \$*[0-9]+

which outputs

    some $1 something >> ['$1']
    some other 115 >> ['115']
    $115 some thing >> ['$115']

If this is not what you want, comment.

Hope this helps.

I would recommend using online regex matchers like here. They have short and sweet references which really help us get to the context.

Improve this answer
$\endgroup$
6
  • $\begingroup$ If I do just regexp_substr(comment_text, '[0-9]*') it won't pull any rows with just a dollar figure, eg $123. $\endgroup$
    – ScottieB
    Commented Dec 15, 2016 at 20:09
  • $\begingroup$ @ScottieB u want number finally or number including the sign as in case 2 of my answer? $\endgroup$
    – Kiritee Gak
    Commented Dec 16, 2016 at 0:59
  • $\begingroup$ no dollar sign. Case 2 is what I'm aiming for $\endgroup$
    – ScottieB
    Commented Dec 16, 2016 at 16:56
  • $\begingroup$ Is number with no dollar is all u want from text even though there exists a dollar, then case 1 should do. With dollar u need the output - case2. Isn't ur comment contradicting? $\endgroup$
    – Kiritee Gak
    Commented Dec 16, 2016 at 19:43
  • $\begingroup$ I tried ur $123 case n the output was 123 via regex 1. $\endgroup$
    – Kiritee Gak
    Commented Dec 16, 2016 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.