|
View previous topic :: View next topic
|
| Author |
Message |
sureshbait
Warnings : 1
New User
Joined: 02 Feb 2007
Posts: 5
Location: Chennai
|
|
|
|
| How to select the rows that having a name field only starts with capital letter? |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi suresh,
Would you please provide the record length & type of input .
Please provide us sample input & output so that we can help u out |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
Since this is in the DB2 forum I will assume there is no record length and you are querying a database table. So you have a NAME column in your table.
Using only that column for illustration:
| Code: |
Name
----------
robert
larry
James
Thomas
richard
Mark
|
You want to select only those records that have a capital letter in position 1 of the data for that column and have the result set look like this?
Did I summarize your problem correctly? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
You could use
| Code: |
SELECT * FROM TABLE WHERE (NAME BETWEEN 'A' AND 'I' ) OR (NAME BETWEEN 'J' AND 'R') OR (NAME BETWEEN 'S' AND 'Z' );
|
or
| Code: |
SELECT * FROM TABLE WHERE SUBSTR(NAME,1,1) = UPPER(NAME,1,1);
|
|
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Does DB2 do case sensitive comparisons by default or is there some option that needs to be switched? I had no idea so I didn't suggest your option 2. But that is how I would do it. |
|
| Back to top |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada
|
|
|
|
I like Craq's 2nd solution but it may not work if the field starts with a number or some other character that has no uppercase equivilant.
Would the following tweak fix this?
| Code: |
SELECT * FROM TABLE WHERE SUBSTR(NAME,1,1) = UPPER(NAME,1,1)
AND UPPER(SUBSTR(NAME,1,1)) <> LOWER(SUBSTR(NAME,1,1)
|
Craq's first solution seems fine... |
|
| Back to top |
|
|
sureshbait
Warnings : 1
New User
Joined: 02 Feb 2007
Posts: 5
Location: Chennai
|
|
|
|
Thank you Craq.
I tried both queries first one is working. 2nd one is not working. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| sureshbait wrote: |
Thank you Craq.
I tried both queries first one is working. 2nd one is not working. |
My error should be
| Code: |
SELECT * FROM TABLE WHERE SUBSTR(NAME,1,1) = UPPER(substr(NAME,1,1));
|
|
|
| Back to top |
|
|
|
|
