CODEWITHSUNDEEP
mysqlspecial-characters
Atwp67
Atwp67

Reputation: 307

MySQL search for "$" (dollar sign) fails?

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 '%$%';

Upvotes: 4

Views: 4731

Answers (2)

Sammitch
Sammitch

Reputation: 32272

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.

Upvotes: 2

Yogendra Singh
Yogendra Singh

Reputation: 34387

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

   SELECT * FROM concept WHERE LOCATE('$', concept_description) > 0;

Upvotes: 3

Related Questions