View previous topic :: View next topic
|
Author |
Message |
Shriram Jogdand
New User
Joined: 14 Oct 2008 Posts: 65 Location: Pune
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
you could do a POSSTR on a RTRIM'd column.
or use STRIP to remove leading spaces. |
|
Back to top |
|
|
apandey
New User
Joined: 31 Aug 2009 Posts: 73 Location: Mumbai
|
|
|
|
Hi Shriram,
I think u can use SUBSTR function to check for spaces. |
|
Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007 Posts: 1742 Location: Tirupur, India
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|