SQL Query beginner needing advice on filtering

Question

I'm trying to put together a query that can filter out a specific special character with the added condition that it will only give you the results if that special character only appears a certain number of times (in this case once).

My current query:

select k.name as 'GroupName'
  , k.Type as 'Factor'
From Kf as k
Join KfChildren as kc on k.ID = kc.id
Join Kf as k1 on kc.ChildID = k1.id
where k.name like ('ASX:%') --special character needed ":"

I'm looking for the query to bring back:

ASX:TRANSPORTATION

and not:

ASX:TSG:PSG:Rollers

Is this something that is doable or should I just try and figure something out?

Thanks in advance for anyone posting any responses to this :)

Could you provide some sample data and expect result? what's your dbms — D-Shih, Jul 30 '18 at 22:41
You may find this useful - https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field — fubar, Jul 30 '18 at 22:44
On SQL Server you could add something like `and charindex(':', k.name, 5) = 0` — shawnt00, Jul 30 '18 at 23:12
Thanks for all the responses, @D-Shih: using Microsoft SQL Server 2012 as for sample data say the database contains: ASX:Transportation and ASX:TSG:PSG:Rollers I just want it to bring back the first one as it only has 1 colon in its name — Masato Indou, Jul 31 '18 at 00:17
@shawnt00: thanks shawn I think that has given me what I needed :) — Masato Indou, Jul 31 '18 at 00:19

score 0 · Answer 1 · edited Jul 30 '18 at 23:04

0

Try this:

WHERE (upper(k.name) LIKE '%ASX:%'
  AND upper(k.name) NOT LIKE '%:%:%:')

edited Jul 30 '18 at 23:04

Tim Diekmann

7,755
11
41
69

answered Jul 30 '18 at 22:55

GoreMassacre

1

Thanks for the response, I tried it and it seems that I'm still getting results that have multiple ':' colons. need to somehow limit the results to those that just have one colon – Masato Indou Jul 30 '18 at 23:16
1

That second pattern allows for two colons but not three. – shawnt00 Jul 31 '18 at 00:03

Jay Desai · Answer 2 · 2018-07-30T23:50:11.093

0

Try This WHERE k.name LIKE 'ASX[:]%' AND k.name NOT LIKE ‘%:%:%’

Mark as solution if works.

edited Jul 30 '18 at 23:50

answered Jul 30 '18 at 23:04

Jay Desai

821
3
15
42

Thanks for the quick reply, I tried this and it looks like I'm still getting results with multiple colons ':' need to somehow limit the results to those that just have one colon – Masato Indou Jul 30 '18 at 23:17

score 0 · Accepted Answer · answered Jul 31 '18 at 00:06

0

Clause: starting with ASX and only one ':'

WHERE k.Name LIKE 'ASX:%'
AND NOT LIKE 'ASX:%:%'

Replace ASX with % if you need the wildcard.

answered Jul 31 '18 at 00:06

Gonzalo Lorieto

635
6
24

SQL Query beginner needing advice on filtering

3 Answers3