IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need to Fetch rows which contains special characters


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
aryanpa1

New User


Joined: 26 May 2007
Posts: 45
Location: Chennai

PostPosted: Fri Feb 29, 2008 12:26 pm
Reply with quote

Hi ,
I need to fetch the rows from a DB2 table which contains special characters in a particular column of length 30. Can you please help...

Special characters means : Not Numeric and Not Alphabetic
It means Other than A to Z and 0 to 9

Ex: ? , % @ #....
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 218
Location: India

PostPosted: Fri Feb 29, 2008 2:43 pm
Reply with quote

i am not getting u correctly...select " column " from table with give the results...
Explain ur problem clearly..
Back to top
View user's profile Send private message
aryanpa1

New User


Joined: 26 May 2007
Posts: 45
Location: Chennai

PostPosted: Fri Feb 29, 2008 2:59 pm
Reply with quote

Example:

Table has following columns

1)Student_id
2)Student_Name

Data in Table
---------------------------------------
Student_id Student_Name
--------------------------------------
1 pavan kumar
2 Anand$kumar
3 satish*%gorti
4 yugandhar
----------------------------------------

I need to fetch only rows with special characters , that is , the result should have the rows with Student_id's '2' and '3' only.
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 218
Location: India

PostPosted: Fri Feb 29, 2008 4:11 pm
Reply with quote

aryanpa1 wrote:
Example:

Table has following columns

1)Student_id
2)Student_Name

Data in Table
---------------------------------------
Student_id Student_Name
--------------------------------------
1 pavan kumar
2 Anand$kumar
3 satish*%gorti
4 yugandhar
----------------------------------------

I need to fetch only rows with special characters , that is , the result should have the rows with Student_id's '2' and '3' only.


What u mean to say is that there may be special characters in the second field at any place.I think this can not be handled with DB2 query.U have to write a cobol program to separate those. Can any one suggest if there is any way other than what i have suggested.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 355
Location: New York

PostPosted: Fri Feb 29, 2008 4:15 pm
Reply with quote

Hi,

I think you can use wild characters in SQL query.

e.g., Student_Name LIKE '%$%' or '%*%' or '%#%' etc.

Please correct me if I am worng
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 218
Location: India

PostPosted: Fri Feb 29, 2008 4:19 pm
Reply with quote

ksk wrote:
Hi,

I think you can use wild characters in SQL query.

e.g., Student_Name LIKE '%$%' or '%*%' or '%#%' etc.

Please correct me if I am worng


Hi KSK,

We can use wild cards but we are not sure of the place of the occurances of the special characters.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 355
Location: New York

PostPosted: Fri Feb 29, 2008 4:37 pm
Reply with quote

Sri,

If we give '%*%', it selects all the rows which have * at any occurence e.g.

abc*123
dhfjdfjd*kdelwe
wjdhh*skjdhskdhsdhsdhs etc.

No need to know about the occurance of the special character. I think whatever you are saying would be done with Wild-Card '_' not with '%'.

KSK
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 218
Location: India

PostPosted: Fri Feb 29, 2008 4:58 pm
Reply with quote

ksk wrote:
Sri,

If we give '%*%', it selects all the rows which have * at any occurence e.g.

abc*123
dhfjdfjd*kdelwe
wjdhh*skjdhskdhsdhsdhs etc.

No need to know about the occurance of the special character. I think whatever you are saying would be done with Wild-Card '_' not with '%'.

KSK

Then i think we have to write so many conditions (one each special char)using OR.
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 Feb 29, 2008 6:15 pm
Reply with quote

Based on the column names I would guess that this is a class assignment. Are you suppose to solve this yourself without outside help?
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Fri Feb 29, 2008 11:17 pm
Reply with quote

Perhaps you can write a UDF (User Defined Function) to scan the string for alphanumerics (and embedded spaces)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Reading dataset in Python - New Line ... All Other Mainframe Topics 22
No new posts Fetch data from programs execute (dat... DB2 3
Search our Forums:

Back to Top