Splitting data which contains carriage returns

[robz8701]

I have a varchar column which contains a full address (inc. carriage
returns). I wish to create a view which will split this into seperate
address lines but I am struggling to write the correct SQL.

For example the address value is,

West Midlands Region
112 Colmore ROW
Birmingham
B3 3AG

I can return the first line by using the following SQL

SELECT SUBSTR(address,1,INSTR(address,CHR(13)))
FROM address_table

How do I cater for the 2nd, 3rd and 4th lines automatically? Also
address records could be 2,3,4 or even 5 lines, it differs so I need
the SQL to automatically cater for this.

Any help would be appreciated.

[Michael Moore]

does it have to be a pure SQL solution or can you create a PL/SQL function?
What version of Oracle?

[Michael Moore]

One way would be to use a REPLACE function;

basically if you have a string like 'this # is my # string where # number sign represents CR'
then do a replace ( and concatinate at the begin and end so that the result looks like:

<x>this</><x>s my </><x>string where </><x>number sign represents CR</x>

do this in an inner select

then in the outer select use XML functions to get the final result

SELECT [some xml functions go here]
   FROM ( SELECT [your concatenate and replace functions go here]
                  FROM mytable;)

I'd work it out but it is a holiday here and I'd rather be playing WOW.

Mike

[robz8701]

A pure SQL solution would be ideal, I am running Oracle9i Enterprise
Edition Release 9.2.0.8.0 - Production.

On 18 Feb, 15:10, "Michael Moore" <michaeljmo...@gmail.com> wrote:
> does it have to be a pure SQL solution or can you create a PL/SQL function?
> What version of Oracle?
>

> On Feb 18, 2008 4:00 AM, robz8701 <robz8...@hotmail.com> wrote:
>
>
>
>
>
> > I have a varchar column which contains a full address (inc. carriage
> > returns). I wish to create a view which will split this into seperate
> > address lines but I am struggling to write the correct SQL.
>
> > For example the address value is,
>
> > West Midlands Region
> > 112 Colmore ROW
> > Birmingham
> > B3 3AG
>
> > I can return the first line by using the following SQL
>
> > SELECT SUBSTR(address,1,INSTR(address,CHR(13)))
> > FROM address_table
>
> > How do I cater for the 2nd, 3rd and 4th lines automatically? Also
> > address records could be 2,3,4 or even 5 lines, it differs so I need
> > the SQL to automatically cater for this.
>

> > Any help would be appreciated.- Hide quoted text -
>
> - Show quoted text -

[sonty]

Hi,

I think,The problem is solvable,with pure sql, only if you the maximum
number of lines possible.

Regards,
Sonty

> > - Show quoted text -- Hide quoted text -

[robz8701]

Can you elaborate at all? i.e. provide an example SQL statement based
on the example I posted in my initial post?

[Michael Moore]

CREATE TABLE t2 AS (SELECT 'west Midelands Region
122 Colmore row
Briminham
B3 #AG'
addr FROM DUAL
UNION ALL
SELECT 'East Wastelands Region
122 Rodger row
Susex
B9 #AXX'addr FROM DUAL);



SELECT EXTRACTVALUE (VALUE (d), '/g') AS rslt
  FROM t2 vs,
       TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE.createxml (   '<row><g>'
                                                       || REPLACE (addr,
                                                                   CHR (10),
                                                                   '</g><g>'
                                                                  )
                                                       || '</g></row>'
                                                      ),
                                    '/row/g'
                                   )
                          )
             ) d;

[robz8701]

Is there a pure SQL solution available?

Ideally I'm looking to create a view from the base address table which
will have seperate columns for each address line. I appreciate I'll
have to specify a number of address lines but the maximum is 6 so I
only need to specify 6 columns.

Any help would be much appreciated.

[Rob Wolfe]

In what way is Mike's solution not "pure SQL"? If you run it you will
see that it does just what you asked. Do not be fooled by reading it
that it generates XML because the end result is NOT XML, it is just
text.

[Michael Moore]

I agree with Rob. What are you defining to be "pure sql". You could probably use the MODEL clause, but MODEL was seen seen in a strip club last night, so I can't vouch for it's purity. ;-)
Mike