SQL to restrict rows by first letter / number

[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.

[dang65]

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

[dang65]

Apart from 3 Mustaphas 3.

[Moscow Mule]

Originally Posted by DimPrawn

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

You are Bob Shawadiwadi AICMFP

Originally Posted by 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.

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

[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.

[DimPrawn]

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

[NickFitz]

Originally Posted by DimPrawn

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.

[EvilWeevil]

Have you considered...

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

[PRC1964]

Originally Posted by NickFitz

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.

[DimPrawn]

Originally Posted by EvilWeevil

Have you considered...

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

No. A good concise answer.