Home » SQL & PL/SQL » SQL & PL/SQL » how to remove dots in the column
| how to remove dots in the column [message #615916] |
Wed, 11 June 2014 03:35  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
select instructions from oe_order_headers_all
where order_number=1234
Mary Lesicko.5025 Southridge Park [b]Dr..Saint[/b] Louis.MO.63129..
And i need to dispaly as below
Mary Lesicko.5025 Southridge Park [b]Dr.Saint[/b] Louis.MO.63129..
|
|
|
|
| Re: how to remove dots in the column [message #615920 is a reply to message #615916] |
Wed, 11 June 2014 03:57  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Do you need the last two dots? If not then simply use replace :
SQL> WITH DATA AS(
2 SELECT 'Mary Lesicko.5025 Southridge Park [b]Dr..Saint[/b] Louis.MO.63129..' str FROM dual
3 )
4 SELECT REPLACE(str, '..','.') col FROM DATA;
COL
-----------------------------------------------------------------
Mary Lesicko.5025 Southridge Park [b]Dr.Saint[/b] Louis.MO.63129.
|
|
|
|
| Re: how to remove dots in the column [message #615921 is a reply to message #615920] |
Wed, 11 June 2014 04:04 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Or, if you are sure that there would be only one instance of '..' dots in between the string, and you want to keep the last two dots, then use SUBSTR :
SQL> WITH DATA AS(
2 SELECT 'Mary Lesicko.5025 Southridge Park [b]Dr..Saint[/b] Louis.MO.63129..' str FROM dual
3 )
4 SELECT SUBSTR(STR, 1, INSTR(STR, '..', 1) - 1) ||
5 SUBSTR(STR, INSTR(STR, '..', 1) + 1) col
6 FROM DATA;
COL
------------------------------------------------------------------
Mary Lesicko.5025 Southridge Park [b]Dr.Saint[/b] Louis.MO.63129..
|
|
|
|
| Re: how to remove dots in the column [message #615926 is a reply to message #615921] |
Wed, 11 June 2014 04:32 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Thanks Lalit.. 
I need to display only CITY from that column and i tried to retrieve only city name
Mary Lesicko.5025 Southridge Park Dr.Saint Louis.MO.63129..
SELECT Substr (instructions, Instr (instructions, '.', 1, 2) + 1, (( Instr (instructions, '.', 1, 3) - 1 ) - Instr (instructions, '.', 1, 2) ))
FROM oe_order_headers_all
WHERE order_number = 1234
But i am getting NULL values , means no rows return.
I need only Saint Louis that is CITY .
|
|
|
|
| Re: how to remove dots in the column [message #615928 is a reply to message #615926] |
Wed, 11 June 2014 04:38 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Null values is not the same as no rows, they are different things and it's doing one or the other.
Looks like the substr works:
SQL> WITH oe_order_headers_all AS (SELECT 'Mary Lesicko.5025 Southridge Park Dr.Saint Louis.MO.63129..' instructions,
2 1234 AS order_number FROM dual)
3 SELECT Substr (instructions, Instr (instructions, '.', 1, 2) + 1, (( Instr (instructions, '.', 1, 3) - 1 ) - Instr (instructions, '.', 1, 2) ))
4 FROM oe_order_headers_all
5 WHERE order_number = 1234
6 /
SUBSTR(INSTRUCTIONS,INSTR(INST
------------------------------
Saint Louis
So it looks like the problem is that no rows match the where clause.
|
|
|
|
| Re: how to remove dots in the column [message #615930 is a reply to message #615928] |
Wed, 11 June 2014 04:44 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
WITH oe_order_headers_all AS (SELECT 'Mary Lesicko.5025 Southridge Park Dr..Saint Louis.MO.63129..' instructions,
1234 AS order_number FROM dual)
SELECT Substr (instructions, Instr (instructions, '.', 1, 2) + 1, (( Instr (instructions, '.', 1, 3) - 1 ) - Instr (instructions, '.', 1, 2) ))
FROM oe_order_headers_all
WHERE order_number = 1234
Thanks cookiemonster, could you please run the same query please. I am getting no rows
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615933 is a reply to message #615930] |
Wed, 11 June 2014 05:13 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Wed, 11 June 2014 10:44
Thanks cookiemonster, could you please run the same query please. I am getting no rows
You are getting a row, with a value of null. Do not confuse that with no rows, they are different problems that require different solutions.
Add each instr separately to the select and check they give the values you expect.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615948 is a reply to message #615940] |
Wed, 11 June 2014 06:15 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Wed, 11 June 2014 16:20How do you know where is the city in your data?
Hi Michel,
Mary America(1).1234 Southridge Park Dr.(2).Samna Lorie(3).MO(4).31234(5).. -->Samna Lorie
Paula Julian(1).363 Revere Beach BLVD(2).Revere(3).MA(4).02151(5)..-->Revere
In the above outputs in the first one 3rd one is the City & second one same 3rd on is the City
SQL> WITH data
2 AS (SELECT
3 'Mary America(1).1234 Southridge Park Dr.(2).Samna Lorie(3).MO(4).31234(5)..'
4 str
5 FROM dual
6 UNION ALL --Samna Lorie
7 SELECT 'Paula Julian(1).363 Revere Beach BLVD(2).Revere(3).MA(4).02151(5)..'
8 FROM dual)--Revere
9 SELECT Substr(str, Instr(str, '(2).', 1) + 4, Instr(str, '(3).', 1) -
10 Instr(str, '(2).', 1) - 4)
11 str
12 FROM data;
STR
---------------------------------------------------------------------------
Samna Lorie
Revere
Edit -- Didn't see Michel already replied
The original question was to remove the extra dots, then in the course the question got changed to get a part of string.
[Updated on: Wed, 11 June 2014 06:30] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615957 is a reply to message #615954] |
Wed, 11 June 2014 07:12 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
SELECT instructions,
REGEXP_SUBSTR(instructions,'[^\.]+',1,1) text1,
REGEXP_SUBSTR(instructions,'[^\.]+',1,2) text2,
REGEXP_SUBSTR(instructions,'[^\.]+',1,3) text3,
REGEXP_SUBSTR(instructions,'[^\.]+',1,4) text4,
REGEXP_SUBSTR(instructions,'[^\.]+',1,5) text5
FROM oe_order_headers_all
WHERE order_number in (1234,1235,1236,1237,1238)
Christine Bast-Friesen(1).200 E. Carrington Lane(2).Appleton(3).WI(4).54913(5)..
In the above Appleton(3)is the city
text1 - ship_to_name
text2 - ship_to_address
text3 - city
text4 - state
text5 - zip
1)Christine Bast-Friesen text1
2)200 E. Carrington Lane text2
3)Appleton (CITY)
4)WI
5)54913
I need the bove format, but i am getting the below format.
1)Christine Bast-Friesen
2)200 E
3) Carrington Lane
4)Appleton
5)WI
[Updated on: Wed, 11 June 2014 07:12] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615968 is a reply to message #615916] |
Wed, 11 June 2014 07:29 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
|
As pointed up in your duplicate thread at OTN, this is a VERY bad design. And your difficulty in solving the problem is a textbook example of WHY it is a bad design. Until you correct the design by properly normalizing the data, you will continue to struggle with this issue. At some point, your organization is simply going to have to fix the root problem and redesign the table by separating the various components of this 'bit bucket column' into properly defined columns: LAST_NAME, FIRST_NAME, MIDDLE_NAME, STREET_ADDRESS, CITY, STATE, ZIP
|
|
|
|
| Re: how to remove dots in the column [message #615969 is a reply to message #615957] |
Wed, 11 June 2014 07:30 |
cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Wed, 11 June 2014 13:12
Christine Bast-Friesen(1).200 E. Carrington Lane(2).Appleton(3).WI(4).54913(5)..
In the above Appleton(3)is the city
In the above the (3) is in the wrong place. You can't stick numbers in to indicate dots and then skip some of the dots.
You might know instinctively from looking at that how to split it but you can't write code based on instinct. Code has to written based on definable rules and you haven't supplied any.
If you can't write out a set of rules that define at what points the string is to be split then no one can write code to do what you want.
You seem to want it to be split on some, but not all, of the dots. If so you need to define a set of rules that will allow us to determine which dots can be ignored for splitting.
|
|
|
|
| Re: how to remove dots in the column [message #615973 is a reply to message #615965] |
Wed, 11 June 2014 07:34 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
mist598 wrote on Wed, 11 June 2014 17:56I need to display the below format
Christine Bast-Friesen(ship_to_name).200 E. Carrington Lane(ship_to_addres).Appleton(city).WI(sate).54913(zip)..
For one last time, I hope this is what you want as ship_to_address :
SQL> WITH data
2 AS (SELECT
3 'Christine Bast-Friesen.200 E. Carrington Lane.Appleton.WI.54913..' str
4 FROM dual)
5 SELECT Substr(str, 1, Instr(str, '.', 1, 1) - 1)
6 ship_to_name,
7 Substr(str, Instr(str, '.', 1, 1) + 1, Instr(str, '.', 1, 3) -
8 Instr(str, '.', 1, 1) - 1)
9 ship_to_addres,
10 Substr(str, Instr(str, '.', 1, 3) + 1, Instr(str, '.', 1, 4) -
11 Instr(str, '.', 1, 3) - 1) city,
12 Substr(str, Instr(str, '.', 1, 4) + 1, Instr(str, '.', 1, 5) -
13 Instr(str, '.', 1, 4) - 1) state,
14 Substr(str, Instr(str, '.', 1, 5) + 1, Instr(str, '.', 1, 6) -
15 Instr(str, '.', 1, 5) - 1) zip
16 FROM data;
SHIP_TO_NAME SHIP_TO_ADDRES CITY STATE ZIP
---------------------- ---------------------- -------- ----- -----
Christine Bast-Friesen 200 E. Carrington Lane Appleton WI 54913
Your biggest problem is that you do not follow normalization.
Edit : Update the code to suffice all the requirements together
[Updated on: Wed, 11 June 2014 07:43] Report message to a moderator |
|
|
|
| Re: how to remove dots in the column [message #615975 is a reply to message #615926] |
Wed, 11 June 2014 07:40 |
 |
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If we can assume there is no dot in zip, state and city, then city can be found with:
SQL> col val format a70
SQL> col city format a15
SQL> with
2 data as (
3 select 'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..' val from dual
4 union all
5 select 'Paula Julian.363 Revere Beach BLVD.Revere.MA.02151..' from dual
6 union all
7 select 'Christine Bast-Friesen.200 E. Carrington Lane.Appleton.WI.54913..' from dual
8 )
9 select val,
10 substr(val,
11 instr(val,'.',-1,5)+1,
12 instr(val,'.',-1,4)-instr(val,'.',-1,5)-1
13 ) city
14 from data
15 /
VAL CITY
---------------------------------------------------------------------- ---------------
Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234.. Samna Lorie
Paula Julian.363 Revere Beach BLVD.Revere.MA.02151.. Revere
Christine Bast-Friesen.200 E. Carrington Lane.Appleton.WI.54913.. Appleton
|
|
|
|
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615981 is a reply to message #615977] |
Wed, 11 June 2014 07:49 |
|
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Lalit Kumar B wrote on Wed, 11 June 2014 14:45Look at http://www.orafaq.com/forum/mv/msg/193117/615973/#msg_615973 I have updated the query.
And it does not work for all posted cases:
SQL> col ship_to_name format a25
SQL> col ship_to_addres format a30
SQL> col city format a15
SQL> col state format a6
SQL> col zip format a5
SQL> with
2 data as (
3 select 'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..' str from dual
4 union all
5 select 'Paula Julian.363 Revere Beach BLVD.Revere.MA.02151..' from dual
6 union all
7 select 'Christine Bast-Friesen.200 E. Carrington Lane.Appleton.WI.54913..' from dual
8 )
9 SELECT Substr(str, 1, Instr(str, '.', 1, 1) - 1)
10 ship_to_name,
11 Substr(str, Instr(str, '.', 1, 1) + 1, Instr(str, '.', 1, 3) -
12 Instr(str, '.', 1, 1) - 1)
13 ship_to_addres,
14 Substr(str, Instr(str, '.', 1, 3) + 1, Instr(str, '.', 1, 4) -
15 Instr(str, '.', 1, 3) - 1) city,
16 Substr(str, Instr(str, '.', 1, 4) + 1, Instr(str, '.', 1, 5) -
17 Instr(str, '.', 1, 4) - 1) state,
18 Substr(str, Instr(str, '.', 1, 5) + 1, Instr(str, '.', 1, 6) -
19 Instr(str, '.', 1, 5) - 1) zip
20 from data
21 /
SHIP_TO_NAME SHIP_TO_ADDRES CITY STATE ZIP
------------------------- ------------------------------ --------------- ------ -----
Mary America 1234 Southridge Park Dr. Samna Lorie MO 31234
Paula Julian 363 Revere Beach BLVD.Revere MA 02151
Christine Bast-Friesen 200 E. Carrington Lane Appleton WI 54913
[Updated on: Wed, 11 June 2014 07:49] Report message to a moderator |
|
|
|
| Re: how to remove dots in the column [message #615982 is a reply to message #615981] |
Wed, 11 June 2014 07:53 |
|
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And even less if someone has a middle initial:
SQL> with
2 data as (
3 select 'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..' str from dual
4 union all
5 select 'Paula Julian.363 Revere Beach BLVD.Revere.MA.02151..' from dual
6 union all
7 select 'Christine Bast-Friesen.200 E. Carrington Lane.Appleton.WI.54913..' from dual
8 union all
9 select 'Michel J. Cadot.13 rue de la paix.Paris.FR.75117..' from dual
10 )
11 SELECT Substr(str, 1, Instr(str, '.', 1, 1) - 1)
12 ship_to_name,
13 Substr(str, Instr(str, '.', 1, 1) + 1, Instr(str, '.', 1, 3) -
14 Instr(str, '.', 1, 1) - 1)
15 ship_to_addres,
16 Substr(str, Instr(str, '.', 1, 3) + 1, Instr(str, '.', 1, 4) -
17 Instr(str, '.', 1, 3) - 1) city,
18 Substr(str, Instr(str, '.', 1, 4) + 1, Instr(str, '.', 1, 5) -
19 Instr(str, '.', 1, 4) - 1) state,
20 Substr(str, Instr(str, '.', 1, 5) + 1, Instr(str, '.', 1, 6) -
21 Instr(str, '.', 1, 5) - 1) zip
22 from data
23 /
SHIP_TO_NAME SHIP_TO_ADDRES CITY STATE ZIP
------------------------- ------------------------------ --------------- ------ -----
Mary America 1234 Southridge Park Dr. Samna Lorie MO 31234
Paula Julian 363 Revere Beach BLVD.Revere MA 02151
Christine Bast-Friesen 200 E. Carrington Lane Appleton WI 54913
Michel J Cadot.13 rue de la paix Paris FR 75117
|
|
|
|
|
|
|
|
| Re: how to remove dots in the column [message #615990 is a reply to message #615989] |
Wed, 11 June 2014 08:26 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
When you have scenario like '200 E. Carrington Lane' as ship_to_address, then you need to handle the same in INSTR. Use CASE construct to get the desired output.
The best solution is to fix the root cause, that is to NORMALIZE your data. It's a bad design to have different values in the same column.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 16:35:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
