Removing blank spaces

[adventurous1]

Ok... I am crying uncle and asking for help after doing my best to find this in any documentation or reference material.

I am concatenating the following ZIP||SUBSTR(UPPER(LNAME),5)||SUBSTR(ADDR1,6).

ZIP=60543, LNAME=JOHNSON, ADDR1=1 MAIN STREET

The result = 60543JOHNS1 MAIN

What I need it to look like = 60543JOHNS1MAIN (with one blank space after main so the total length = 16 bytes)

Since the address portion can have spaces (and they arent removed by rtrim or ltrim) the resulting record has spaces in it.

I couldnt find a function that would do this... Can anyone help?

Thanks!

 

[oradba101]

Hi adventurous1

Perhaps the INSTR function?

Regards,


William Chadbourne
Oracle DBA

 

[SantaMufasa]

adventurous, Let me make sure that I understand what you want: 1) You want the space that exists between "1 Main" to go away (becoming "1Main"), and 2) You want a blank space appended to the end of "1Main" to become "1Main "?

I believe we are missing some specs here...If my address is "10037 Bell Canyon Rd." then would you want "10037 " for output (with a blank following the house number, and no street name resulting?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[adventurous1]

Thank you both for your help. Some additional info and another question...

What is the correct usage of INSTR?

If the address is "10037 Bell Canyon Rd." then the output should be "10037B". A blank space should only exist if that is the next character to fulfill the 6th position.

Hope this clarifies...

 

[SantaMufasa]

Select instr('abcdefabcdefabcdef','def')...

means: What position in the string 'abcdefabcdefabcdef' does one find 'def'? Result: 4

Select instr('abcdefabcdefabcdef','def',1,2)...

means: Beginning at character #1, what position in the string 'abcdefabcdefabcdef' does one find occurrence #2 of 'def'? Result: 10

Select instr('abcdefabcdefabcdef','def',11,1)...

means: Beginning at character #11, what position in the string 'abcdefabcdefabcdef' does one find occurrence #1 of 'def'? Result: 16

Select instr('abcdefabcdefabcdef','def',17,1)...

means: Beginning at character #17, what position in the string 'abcdefabcdefabcdef' does one find occurrence #1 of 'def'? Result: 0

Let us know if this answers your questions.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[adventurous1]

That may be the usage of INSTR but it doesnt provide any insight into how to solve my original problem:

If the address is "10037 Bell Canyon Rd." then the output should be "10037B". A blank space should only exist if that is the next character to fulfill the 6th position.

Any thoughts?

Thanks in advance for the guidance.

 

[SantaMufasa]

I understand now what you want with the address, but how would you like these data to appear when strung together:

ZIP=60543, LNAME=PO, ADDR1=1 MAIN STREET

?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Also, Adventurous, can you enlighten us as to why
[tt]
"1MAIN " is preferrable to
"1MAINS" ?[/tt]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

...My point being, if [tt]
"1 MAIN STREET"[/tt] should produce
[tt]"1MAIN "[/tt] as its correct result,
then
[tt]"10037 BELL CANYON RD."[/tt] should produce
[tt]"10037 "[/tt] as its correct result, right?

My suggestion is that if your application can accommodate it, then the simplest solution is to

a) squeeze all of the blanks out of the address
b) take up to 6 positions of that (blank-squeezed) result
c) right space pad, if needed, to bring the result to 6 positions.

If we follow the above algorithm, then the respective results would be (for the address portion):
[tt]
"1MAINS" and
"10037B"[/tt]

Please let us know your thoughts on this clarification.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Since it is 00:37 hrs. here, and I'm getting sleepy, rather than wait for your confirmation, I'll just post some code that will handle either option for your:

select * from adventure;

ZIP   LNAME                          ADDR1
----- ------------------------------ --------------------
00054 Po                             10037 Bell Canyon Rd
60534 Johnson                        1 Main Street

REM Option 1...
col x heading "16-chr. String|of ZIP, Name|and Address" format a16
select upper(zip
           ||substr(lname||'     ',1,5)
           ||substr(translate(substr(addr1,1,6),'A ','A')||'      ',1,6)) x
  from adventure;

16-chr. String
of ZIP, Name
and Address
----------------
00054PO   10037
60534JOHNS1MAIN

REM Option 2...
select upper(zip
           ||substr(lname||'     ',1,5)
           ||substr(translate(addr1,'A ','A')||'      ',1,6)) x
  from adventure;

16-chr. String
of ZIP, Name
and Address
----------------
00054PO   10037B
60534JOHNS1MAINS

Let us know if either is a satisfactory outcome for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

How about

select rpad(replace(ZIP||SUBSTR(UPPER(LNAME),5)||SUBSTR(ADDR1,6),' '),16,' ')
from my_table;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

It's hard to know which of our options work for Adventurous1 until s/he gets back to us.

For the casual reader of this thread, the difference between Bill's code and mine is:

1) If a LNAME is shorter that 5 positions, my code right pads the LNAME to reach 5 positions, whereas Bill's code adds the right padding at the end of the 16-position string.

2) My code uses the TRANSLATE function to squeeze out blanks, whereas Bill's code uses the REPLACE function. (I like Bill's REPLACE over my TRANSLATE.)

So, Adventurous1, let us know about your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[adventurous1]

ok guys... i appreciate the help... here is some additional info...

Originally, I did provide this key first by removing the spaces and then doing the substring but the procedure that I am using belongs to an existing process that uses the input including the spaces as a key. (Poor design but I dont control so cant change...)

Now, back to the original question...

I am concatenating the following ZIP||SUBSTR(UPPER(LNAME),5)||SUBSTR(ADDR1,6).

ZIP=60543, LNAME=JOHNSON, ADDR1=1 MAIN STREET

The result = 60543JOHNS1 MAIN

What I need it to look like = 60543JOHNS1MAIN (with one blank space after main so the total length = 16 bytes)

For your example of ZIP=60543, LNAME=PO, ADDR1=1 MAIN STREET, the result should look like 60543POxxx1 MAIN where the "x" is equivalent to a space.

Bill's solution looks good but I also need to trap for instances that Mufasa pointed out where the zip code, last name or address may be less than each elements substring length. Can you guys help?

 

[SantaMufasa]

A couple of followup questions before proposing a "final" solution for you...

1) Do you store your ZIP codes in a NUMBER column or are they in a VARCHAR column?

2) If a LNAME is "ST JO", do you want that component to appear as "STJO " or as "ST JO"?

3) In summary, you want these components:
a) ZIP = 5 chrs.
b) LNAME = 5 chrs.
c) ADDR = 6 chrs.
Correct?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[adventurous1]

Hi..

1. I believe they are varchar2 to accommodate canadian postal codes.

2. If a LNAME is "ST JO", it should appear as "ST JO". (Again, poor design but I cannot change...)

3. Correct, with spaces as in the original strings.

Thanks again for your help!

 

[SantaMufasa]

Sorry to beat this poor dead horse, but is it true that you want:

1) right padding, as needed, on all three fields, to bring each field up to its appropriate length (i.e., ZIP = 5 chrs., LNAME = 5 chrs., and ADDR = 6 chrs.)

2) internal space compression should occur on addresses only?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]