4

Been asked to find dollar values across a large body of text. Can a search be performed on the dollar sign? If so, any examples/guidance would be most appreciated. Current query...

select * from concept where concept_description like '%$%';
Atwp67
  • 307
  • 5
  • 21
  • yes, no returns but when I eye-balled snippets of the text I saw a couple of dollar values. – Atwp67 Oct 23 '12 at 17:23
  • There is no special meaning of `$` in a LIKE (or a SQL string literal). Unless the client sending the query is doing something funny with `$` - eeps! - I would imagine that the data and perceptions are not aligning .. –  Oct 23 '12 at 17:25
  • @user1753646 Try juergen's query and compare the results. They should be the same. –  Oct 23 '12 at 17:28
  • @juergen thanks for the query but zero returns. I knew this was going to be ugly...legacy data. – Atwp67 Oct 23 '12 at 17:30
  • It should work. Must be something else. http://sqlfiddle.com/#!2/c34b9/1 – juergen d Oct 23 '12 at 17:32
  • @juergen thanks for the query but zero returns. I knew this was going to be ugly...legacy data. – Atwp67 Oct 23 '12 at 17:33
  • @user1753646 Then I suspect - it's not *really* `$`s or the collation is all entirely broken in every sense of broken :) I've also "assumed" that the correct table/column are used in the query .. do queries involving "normal characters" work? –  Oct 23 '12 at 17:33
  • @pst correct table/column name. – Atwp67 Oct 23 '12 at 17:37
  • @pst queries involving "normal characters" do work – Atwp67 Oct 23 '12 at 17:38
  • 2
    How are you executing the query? It's possible your language is doing string interpolation on this if you're not careful. – tadman Oct 23 '12 at 18:41

2 Answers2

3

You may want to use LOCATE to see if the col value contains the $ e.g.

   SELECT * FROM concept WHERE LOCATE('$', concept_description) > 0;
Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
2

The queries given will select the rows where concept_description contains a $, but I assume that you want to actually pull out the dollar amounts? If there's only ever just one dollar amount in a field it can be pulled out using

SELECT
SUBSTRING(
    concept_description,
    LOCATE('$', concept_description),
    LOCATE(' ', concept_description, LOCATE('$', concept_description)) - LOCATE('$', concept_description)
)
FROM table
WHERE LOCATE('$', concept_description) > 0

This assume that the dollar amount is always followed by a space, and might need some fudging on the indexes. It's probably best to pull the full field out with a simple query, then use regular expressions to grab any dollar values.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • thanks for the code but no initial returns. I'll play with the code a little and let you know the outcome. – Atwp67 Oct 23 '12 at 17:46
  • Besides having the arguments to [`LOCATE`](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate) mixed up (the needle comes *first*), why not just use `LIKE`? –  Oct 23 '12 at 18:50
  • @pst I think I may have figured this out using a regular experessions but need a devils advocate `select * from concept where concept_description rlike '\\$';` – Atwp67 Oct 23 '12 at 19:53
  • Note: all of the `LOCATE()`s in my post had their arguments backwards. I've fixed them. The `RLIKE` you've given seems to work, also it seems to be faster than `LOCATE()`. – Sammitch Oct 23 '12 at 20:10
  • @Sammitch Ran the code you fixed up and it worked nicely, thanks! – Atwp67 Oct 23 '12 at 20:14