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

Selecting column with 2 or more spaces


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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: 1742
Location: Tirupur, India

PostPosted: Mon May 30, 2011 5:27 pm
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
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: 1281
Location: Belgium

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

just read about an interesting technique at C.Mullins : 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts leading spaces can be removed in trai... DFSORT/ICETOOL 1
No new posts first column truncated in search result IBM Tools 13
No new posts Cobol program with sequence number ra... COBOL Programming 5
Search our Forums:

Back to Top