karl101 — 2010-12-24T11:18:25-05:00 — #1
I have a list if VARCHARS, like this:
Can anyone say how I can select the items that begin with the number one? So I get:
but none of the others. SELECT * FROM table WHERE thing LIKE '%1%' won't work as that will pick C10, 21, and D11 as well
guido2004 — 2010-12-24T11:25:19-05:00 — #2
C1 doesn't begin with the number 1?
To get the ones that start with 1, use '1%' instead of '%1%'
karl101 — 2010-12-24T11:42:55-05:00 — #3
Sorry, I wasn't clear. I want C1 to be included in the results, but not C10.
r937 — 2010-12-24T11:44:59-05:00 — #4
FROM LEAST(NULLIF(POSITION('0' IN str),0)
,NULLIF(POSITION('1' IN str),0)
,NULLIF(POSITION('2' IN str),0)
,NULLIF(POSITION('3' IN str),0)
,NULLIF(POSITION('4' IN str),0)
,NULLIF(POSITION('5' IN str),0)
,NULLIF(POSITION('6' IN str),0)
,NULLIF(POSITION('7' IN str),0)
,NULLIF(POSITION('8' IN str),0)
,NULLIF(POSITION('9' IN str),0))
FOR 1 ) = '1'
guido2004 — 2010-12-24T11:45:59-05:00 — #5
So you want only those lines where '1' has no number in front or behind? I think you'll have to use a regex, but I can't tell you how.
r937 — 2010-12-24T11:47:30-05:00 — #6
omg i just saw your clarification
your requirement said "starts with 1" so why not C10?
and is 10 to be returned or not?
perhaps you ought to clarify the real situation you are working on
p.s. yes i tested my solution on your initial data and requirement
r937 — 2010-12-24T11:50:06-05:00 — #7
guido, the regex is actually easy
but i want to hear the real requirements
karl101 — 2010-12-24T11:52:39-05:00 — #8
I'm working on a bus timetable hack. So when someone is searching for the no 4 bus, they won't be wanting results for the 44, 48, 84 routes. However some buses have a leading character: C4, H4 etc, So I want those listed as well.
guido2004 — 2010-12-24T11:57:02-05:00 — #9
WHERE (thing = '1' OR
thing LIKE '1%' OR
thing LIKE '%1'
r937 — 2010-12-24T12:13:44-05:00 — #10
WHERE thing LIKE '%1'
there are no suffixes
and of course substitute the bus number (e.g. 44) in place of the 1
karl101 — 2010-12-24T18:47:48-05:00 — #11
Thanks for the responses, but neither of those worked how I wanted. I have been able to store the search along with the results, so a repeat search gets the same results.
guido2004 — 2010-12-25T04:50:39-05:00 — #12
You're right, my last solution is just your OP solution split in 3. Since you want the busline with optionally the non numeric pre- and suffixes, regex is the way to go.