Similar To function in sql

Question

I am working on the query

(Select d_name from Direction where d_name  SIMILAR TO '(WEST|NORTH)%' )
this gives me the result as 
WEST 1
WEST 2
NORTH 2
WEST 
NORTH

But I want the result as

WEST
NORTH

with the same statement Similar To. Someone please modify the same query to get the 2nd result. Thanks

Eh? You want to use similar to despite the fact that what you're actually looking for is exact matches? Isn't that a bit like seeking help with using a hammer to drive screws? — Damien_The_Unbeliever, Jul 06 '16 at 07:27
@Damien_The_Unbeliever the query demand similar to. somewhere i need to select all the cols which is possible in similar to and not in like. — dhS, Jul 06 '16 at 07:31
Depending on the dbms used, I'd try replace_regexp instead, to remove the trailing numbers. Use IN on that result. — jarlh, Jul 06 '16 at 07:35

score 3 · Answer 1 · answered Jul 06 '16 at 07:18

3

(select 'WEST' from Direction where d_name  SIMILAR TO 'WEST%')
UNION
(select 'NORTH' from Direction where d_name  SIMILAR TO 'NORTH%');

answered Jul 06 '16 at 07:18

ddb

2,423
7
28
38

Thanks for you answer. I got the answer. – dhS Jul 06 '16 at 07:29
@dhS, you're welcome :) if you want you can accept my answer if solved you're question – ddb Jul 06 '16 at 07:32
I upvoted it ... can't accept as it is long way i need a shorter query. – dhS Jul 06 '16 at 07:33

dhS · Accepted Answer · 2016-08-19T10:35:35.437

1

I got the solution

Select d_name 
from Direction 
where d_name  SIMILAR TO '(WEST|NORTH)'

Just remove % from the query.

edited Aug 19 '16 at 10:35

answered Jul 06 '16 at 07:28

dhS

3,739
5
26
55

1

but, for example, if 'WEST' value is not existing in any row of the table, but you only have 'WEST 1' kind of values, you will not have 'WEST' as results with your solution – ddb Jul 06 '16 at 07:45
@ddb: my understanding is that dhS _does_ want an "exact" match. But if a "wildcard" match is required, the wildcard would need to be added to each value: `SIMILAR TO '(WEST%|NORTH%)'` – Jul 06 '16 at 07:51
1

@a_horse_with_no_name, it's not anymore clear for me what he/she wants :) I thought I had a clear idea, but I don't now :) that's fine if he/she is happy of his/her own solution – ddb Jul 06 '16 at 07:55

score 1 · Answer 3 · edited May 23 '17 at 12:09

If you are using MySQL, as it does not feature a split string function, you can create a user defined function for this, such as the one described in the following article:

MySQL Split String Function by Federico Cargnelutti

With that function, you would be able to build your query as follows:

SELECT DISTINCT SPLIT_STR(d_name, ' ', 1)
FROM Direction
WHERE d_name SIMILAR TO '(WEST|NORTH)%';

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(d_name, ' ', 1), ' ', -1)
FROM Direction
WHERE d_name SIMILAR TO '(WEST|NORTH)%';

Disclaimer: I based this new answer on this solution

Similar To function in sql

3 Answers3