Reply to: SQL to restrict rows by first letter / number

[DimPrawn]

Have you considered...

SELECT band_name
FROM bands
WHERE band_name like (replace(@p,'#','[0-9]')+'%')

No. A good concise answer.

[PRC1964]

This is why one should use metasynctactic variables:

"Say you want to select all the rows in table "foo" having a value in the varchar field "bar" beginning with a specific character from a particular range of characters, or beginning with any character not in that range if some wildcard character is specified..."

That way people don't get distracted talking about nonsense like "How many bands are there in the world" and suchlike - they remain focused on solving the specific problem for the abstract case.

I tend to create tables called "Select" with fields called "From" and "Order By".

It confuses the hell out of everyone else though.

[EvilWeevil]

Have you considered...

SELECT band_name
FROM bands
WHERE band_name like (replace(@p,'#','[0-9]')+'%')

[NickFitz]

Actually it's not bands. I just used that as an example.

This is why one should use metasynctactic variables:

"Say you want to select all the rows in table "foo" having a value in the varchar field "bar" beginning with a specific character from a particular range of characters, or beginning with any character not in that range if some wildcard character is specified..."

That way people don't get distracted talking about nonsense like "How many bands are there in the world" and suchlike - they remain focused on solving the specific problem for the abstract case.

[DimPrawn]

Actually it's not bands. I just used that as an example.

[lilelvis2000]

How many bands are you tracking? 100K? 1M? wow!

Besides you could just make a function index in oracle, or calculated column in SQL server and index it.

[Moscow Mule]

That's in module 2, to be learned next week.

You are Bob Shawadiwadi AICMFP

Actually, I was thinking that the SQL using the OR and LIKE and SUBSTR is not going to be very efficient and is going to scan the data.

I am thinking of maintaining another column (via a trigger) to contain the first letter of the band name and if it is not a letter, store # in the field, so the SQL becomes:

SELECT band_name
FROM bands
WHERE band_name_begins = @p

And put an index on this column, as the data is rarely updated or added to, but read A LOT.

Steady now, you'll start to look like a professional (and I don't mean Bodie or Doyle).

[dang65]

Apart from 3 Mustaphas 3.

[dang65]

There are no good bands which start with a number anyway. I'd just dump them all if I was you.

[DimPrawn]

Actually, I was thinking that the SQL using the OR and LIKE and SUBSTR is not going to be very efficient and is going to scan the data.

I am thinking of maintaining another column (via a trigger) to contain the first letter of the band name and if it is not a letter, store # in the field, so the SQL becomes:

SELECT band_name
FROM bands
WHERE band_name_begins = @p

And put an index on this column, as the data is rarely updated or added to, but read A LOT.

[DimPrawn]

But you can't do an OR?

That's in module 2, to be learned next week.

[Moscow Mule]

I know SQL very well

Now that's what I'm talking about!

But you can't do an OR?

[DimPrawn]

Assuming that you pass in @P and have validated it, then something like the following should work.

SELECT band_name
FROM bands
WHERE 
(@P = '#' AND LEFT(band_name,1) NOT LIKE [a-z])
OR 
(@P != '#'  AND LEFT(band_name,1) = @P)

Now that's what I'm talking about!

[rsingh]

Assuming that you pass in @P and have validated it, then something like the following should work.

SELECT band_name
FROM bands
WHERE 
(@P = '#' AND LEFT(band_name,1) NOT LIKE [a-z])
OR 
(@P != '#'  AND LEFT(band_name,1) = @P)

[DimPrawn]

SELECT band_name
FROM bands
WHERE UPPER(substr(band_name,1,1)) < 'A'
OR UPPER(substr(band_name,1,1)) > 'Z'

This uses Oracle's UPPER(string) to convert to upper case, your flavour of SQL will doubtless have something like it (I presume that you want a case-independent test).

Great but you haven't read the requirement have you?

There is one parameter, either the letter I want or everything that does not start with a letter (# passed in) in one simple, efficient, fast SQL statement.

I know SQL very well and it's pretty obvious how to select everything that's not A to Z.