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
 

 

Selecting column with 2 or more spaces

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Shriram Jogdand

New User


Joined: 14 Oct 2008
Posts: 65
Location: Pune

PostPosted: Mon May 30, 2011 3:29 pm    Post subject: Selecting column with 2 or more spaces
Reply with quote

Hi,

I have one column as LAST_NAME. It may contain 2 word in it.
For eg. 'ABC XYZ'

Generally, both the words in this column will be seperated by a single space. Now I want to see where there exists any record with 2 or more spaces in between these 2 words for that column.

Can we do this through any query?
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon May 30, 2011 3:40 pm    Post subject:
Reply with quote

you could do a POSSTR on a RTRIM'd column.

or use STRIP to remove leading spaces.
Back to top
View user's profile Send private message
apandey

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Mon May 30, 2011 3:41 pm    Post subject: Reply to: Selecting column
Reply with quote

Hi Shriram,

I think u can use SUBSTR function to check for spaces.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1458
Location: Azeroth

PostPosted: Mon May 30, 2011 5:27 pm    Post subject:
Reply with quote

Hello,

Would this query suffice?

Code:
select last_name from table where trim(name) ? '  ';


I am not sure if the trim function exists on DB2 though.


Tested on PROC SQL for SAS


Hope it helps.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon May 30, 2011 9:27 pm    Post subject:
Reply with quote

STRIP (has parms to determine whether 'L' leading, 'T' trialing or 'B' both)
default (with no parm is both)

RTRIM (trims trailing spaces)
LTRIM (trims leading spaces)

will have to test
Quote:

select last_name from table where trim(name) ? ' ';


I am unfamiliar with the ? syntax.

in spufi i would use:
Code:

SELECT key-columns-so-row-can-be-identified
          , LAST_NAME
FROM your-table
WHERE POSSTR(STRIP(LAST_NAME),'  ') > 0


not sure how I would code a SUBSTR. Would require knowing length of LAST_NAME and howmany leading/trailing spaces are involved.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 01, 2011 2:07 pm    Post subject:
Reply with quote

just read about an interesting technique at C.Mullins : http://www.craigsmullins.com/dbta_112.htm
Code:
update EMPLOYEE
     set NAME = replace(
                 replace(
                  replace(NAME, space(1), '<>')
                  '><', space(0))
                 '<>', space(1));

will replace all multiple spaces into 1 space:

Step 1 : replace space with <> : space(3) => '<><><>'
Step 2 replace >< into '' : '<><><><>' => '<>'
step 3 replace '<>' with space => '<>' => ' '
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am


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