MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

STRING edits

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
spalanis
Beginner


Joined: 19 Jul 2006
Posts: 32
Topics: 15
PostPosted: Thu Aug 10, 2006 8:29 am    Post subject: STRING edits Reply with quote
Hi friends,

I have a following task in hand.

Mine is a DB2 database. I have a field which is CHARACTER (10).

I need to find out the rows which has :
an ALPHABET anywhere in this field. ex : 123ABC12, 1A11111...etc.
3 or more consecutive numbers. Ex (123ABC, ABC111111,A123B23456...etc)

How should I accomplish this using SQL query?.

Any of your small input is appreciated.

Thanks,
Srini
Back to top
View user's profile Send private message
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Thu Aug 10, 2006 5:19 pm    Post subject: Reply with quote
One way I could think of is this:

Code:

SELECT * FROM TABLE
WHERE COL1 LIKE '%A%' OR COL1 LIKE '%B%' and so on....until OR COL1 LIKE '%Z%'

That would mean 25 OR's in the where clause

The other example could be extended in similar way except that you would mean 999 combinations.

There are other ways to do it but not through SQL alone.
________
magic flight

Last edited by coolman on Sat Feb 05, 2011 1:47 am; edited 1 time in total
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
PostPosted: Thu Aug 10, 2006 6:39 pm    Post subject: Reply with quote
spalanis,

The following Sql will give you the desired results (untested sql's)

1.

Code:

SELECT *                                                       
  FROM TABLE                                                     
 WHERE STRIP(TRANSLATE(tbl_col,'$','1234567890','$'),B,'$') > ' '
  ;                                                           


2.
Code:

SELECT *
 FROM TABLE
 WHERE SUBSTR(STRIP(TRANSLATE(tbl_col,'$',                             
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ','$'),B,'$'),1,1) NOT IN ('$',' ')
   AND SUBSTR(STRIP(TRANSLATE(tbl_col,'$',                             
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ','$'),B,'$'),2,1) NOT IN ('$',' ')
   AND SUBSTR(STRIP(TRANSLATE(tbl_col,'$',                             
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ','$'),B,'$'),3,1) NOT IN ('$',' ')
 ;


Hope this helps....

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
spalanis
Beginner


Joined: 19 Jul 2006
Posts: 32
Topics: 15
PostPosted: Fri Aug 11, 2006 8:39 am    Post subject: Reply with quote
Thanks Kolusu.....I will try this one.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12401
Topics: 75
Location: San Jose
PostPosted: Mon Aug 14, 2006 10:27 am    Post subject: Reply with quote
spalanis wrote:
Thanks Kolusu.....I will try this one.


Can you verify if the SQL worked for you ?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group