Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To select records that name field start with only caps

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sureshbait
Warnings : 1

New User


Joined: 02 Feb 2007
Posts: 5
Location: Chennai

PostPosted: Thu Sep 06, 2007 5:59 pm    Post subject: To select records that name field start with only caps
Reply with quote

How to select the rows that having a name field only starts with capital letter?
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Sep 06, 2007 6:10 pm    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Sep 06, 2007 7:08 pm    Post subject:
Reply with quote

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?

Code:

James
Thomas
Mark


Did I summarize your problem correctly?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Sep 06, 2007 7:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Sep 06, 2007 7:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Thu Sep 06, 2007 9:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sureshbait
Warnings : 1

New User


Joined: 02 Feb 2007
Posts: 5
Location: Chennai

PostPosted: Fri Sep 07, 2007 1:22 pm    Post subject:
Reply with quote

Thank you Craq.

I tried both queries first one is working. 2nd one is not working.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Sep 07, 2007 5:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to Clear all TSQs during CICS sta... Hooman24 CICS 10 Mon Jun 26, 2017 9:46 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us