Sercing by the first number?

Databases
1

Hello

I have a list if VARCHARS, like this:

1
1A
C2
C1
C10
10
21
D11

Can anyone say how I can select the items that begin with the number one? So I get:

1
1A
C1

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

Thanks

Karl.

2

C1 doesn’t begin with the number 1?

To get the ones that start with 1, use ‘1%’ instead of ‘%1%’

3

Sorry, I wasn’t clear. I want C1 to be included in the results, but not C10.

Karl.

4 
SELECT str
  FROM strings
 WHERE SUBSTRING(str 
        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'

:slight_smile:

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.

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

7

guido, the regex is actually easy

but i want to hear the real requirements

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.

Thanks

Karl.

9 

WHERE (thing = '1' OR
       thing LIKE '1%' OR
       thing LIKE '%1'
      )
10 
WHERE thing LIKE '%1'

there are no suffixes

and of course substitute the bus number (e.g. 44) in place of the 1

:slight_smile:

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.

Karl.

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.