SQL regex with ¶ sign

Question

I need to write a SQL statement to find matching tags. The problem is, that in the database column the words are stored like this:

¶Klimawandel¶Ökosystem¶Moose¶CO2¶Stickstoffkreislauf¶

So if I want to find the word reis and in one column is the word Stickstoffkreislauf it will match, because reis is part of Stickstoffkreislauf.

Therefore I want to write a regex to match the 3 cases:

starts with ¶
ends with ¶
starts and ends with ¶

But unfortunately I have no idea where to start with the regex because of the ¶ in the database. Does anyone have an idea how to start this? Thank you!

Here is my statement so far:

SELECT DISTINCT csia.cID, csia.ak_tags, p.cParentID, cv.cvName 
FROM CollectionSearchIndexAttributes csia 
JOIN Pages p ON csia.cID = p.cID 
JOIN CollectionVersions cv ON csia.cID = cv.cID 
WHERE cv.cvisApproved = '1' 
AND csia.ak_tags like '%reis%'

The column I'm looking for is csia.ak_tags.

¶ is unicode 00B6

i didn't find any differences in your 3rd and above two cases. — Avinash Raj
– Avinash Raj, Commented Jun 30, 2014 at 17:20
@AvinashRaj yes, the last case would be sufficient for this example. But I'm thinking about what happens when in one column there is no ¶ at the beginning or ending of the data. — Sebsemillia
– Sebsemillia, Commented Jun 30, 2014 at 17:25

Community · Accepted Answer · 2017-05-23 12:20:45Z

2

try a like clause akin to this:

csia.ak_tags like '%\\\\u00B6reis\\\\u00B6%'

untested, but feels like it may work. If you get this going, adjusting for a missing trailing ¶ or a missing leading ¶ is a non-issue.

update: http://sqlfiddle.com/#!2/a2269/33/0

select * from csia
where ak_tags like concat('%',0xC2B6,'reis',0xC2B6,'%');

comments in Unicode escape sequence in command line MySQL were helpful

update #2, turns out we were just looking for newlines:

select * from CollectionSearchIndexAttributes
where ak_tags like '%\nreis\n%';

http://sqlfiddle.com/#!2/02f3a/3/0

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jun 30, 2014 at 17:28

Andreas

5,1562 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Andreas Over a year ago

can you confirm the unicode value fields are delimited with? I was guessing 00B6, but I might be wrong. Also, what collation is the column/table under?

Sebsemillia Over a year ago

the collation is utf8_general_ci

Sebsemillia Over a year ago

and I'm sorry, I'm not quite sure what you mean with can you confirm the unicode value fields are delimited with?. Can you paraphrase that? Thank you!

Andreas Over a year ago

the ¶ symbol is being used to delimit tags in the ak_tags field. Is your ¶ \u00B6 or some other unicode char that looks the same but has a different code?

Andreas Over a year ago

perhaps this char code tool will help: babelstone.co.uk/Unicode/whatisit.html

|

Jenn · Accepted Answer · 2014-06-30 17:54:03Z

0

Have you tried using the literal character? Sql is pretty amazing and using like '%reis%¶' should do the trick. Check out this SQL Fiddle that I made for testing purposes.

answered Jun 30, 2014 at 17:54

Jenn

7951 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL regex with ¶ sign

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related