Alphabetical Select Statement
-
I am unsure about how to code in SQL the following select procedure. I have a list of companies, around 500, and I want to display them in a usefull way on my site. I have proposed a A-D E-H I-L etc system where the user can select just a chunk of the alphabet to display. Therefore I am going have some form of range parameter that I will be adding to the SQL query. What would be the best way to create this code in a modular fashion so that I could fire different variations of ranges at it without having to pre set them all up. I would guess the code would ustilise the LIKE keyword with a wildcard after it but how would I create the prefix. I have never heard of any Alphabetical objects in SQL. Any help would be greatly appreciated. Thanks
-
I am unsure about how to code in SQL the following select procedure. I have a list of companies, around 500, and I want to display them in a usefull way on my site. I have proposed a A-D E-H I-L etc system where the user can select just a chunk of the alphabet to display. Therefore I am going have some form of range parameter that I will be adding to the SQL query. What would be the best way to create this code in a modular fashion so that I could fire different variations of ranges at it without having to pre set them all up. I would guess the code would ustilise the LIKE keyword with a wildcard after it but how would I create the prefix. I have never heard of any Alphabetical objects in SQL. Any help would be greatly appreciated. Thanks
You could have a computed field that contains just the first letter, then query for range letters off that computed field.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
You could have a computed field that contains just the first letter, then query for range letters off that computed field.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Sorry I don't follow this. Could you explain, possibly using some code, this solution in more depth. I really want to be able to pass a parameter of say "AD" and then create some code that could work out the rest of the letters needed and then perform something like the following.
SELECT * FROM SOMEWHERE WHERE nameofperson LIKE a% AND nameofperson LIKE b% AND nameofperson LIKE c% AND nameofperson LIKE d% -
Sorry I don't follow this. Could you explain, possibly using some code, this solution in more depth. I really want to be able to pass a parameter of say "AD" and then create some code that could work out the rest of the letters needed and then perform something like the following.
SELECT * FROM SOMEWHERE WHERE nameofperson LIKE a% AND nameofperson LIKE b% AND nameofperson LIKE c% AND nameofperson LIKE d%Hi Sam Assuming that you are using SQL-Server, try:
select * from somewhere where nameofperson like '[ABCDEF]%'You might want to check that you have a case-insensitive collation set for your database - otherwise you will need to use the "Upper()" function around your "nameofperson" column. Regards Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
-
Hi Sam Assuming that you are using SQL-Server, try:
select * from somewhere where nameofperson like '[ABCDEF]%'You might want to check that you have a case-insensitive collation set for your database - otherwise you will need to use the "Upper()" function around your "nameofperson" column. Regards Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
Hey good work, it works. I didn't think it would be that simple. Turns out it does pull both lower and uppper case records as well. Thanks for your help.
-
Sorry I don't follow this. Could you explain, possibly using some code, this solution in more depth. I really want to be able to pass a parameter of say "AD" and then create some code that could work out the rest of the letters needed and then perform something like the following.
SELECT * FROM SOMEWHERE WHERE nameofperson LIKE a% AND nameofperson LIKE b% AND nameofperson LIKE c% AND nameofperson LIKE d%Here is what I was thinking of:
SELECT * FROM sometable WHERE ( Left( someField,1) >= 'A' AND Left( someField,1) <='D' )This would return all rows insometablewhere the first letter is A, B C, D. To do something like AD, you could just have this query in a stored procedure and have it parse your parameter A and D in this case, and plug it into the WHERE clause..."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Hey good work, it works. I didn't think it would be that simple. Turns out it does pull both lower and uppper case records as well. Thanks for your help.
Andy's solution is a very good one :)
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Andy's solution is a very good one :)
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Yeah i think it kind of trumps yours for the fact that I can skip letters out in the way he proposes. Thanks for your solution though. It does indeed produce the results I require.
-
Yeah i think it kind of trumps yours for the fact that I can skip letters out in the way he proposes. Thanks for your solution though. It does indeed produce the results I require.
Andy's works fine as long as you aren't asked to do something like A-Q :)
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon