Parsename Query Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parsename Query Order

Author

Topic

mflammia
Starting Member

44 Posts

Posted - 2008-06-05 : 13:21:48

Greatful for any help....

Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row

Queen Elizabeth's Hunting Lodge is in Address1
All Saints' Church is in Address2
Audley End House is in Address3

As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?

Thanks in advance

SELECT coalesce (PARSENAME(REPLACE(_Venue,',', '.'),4), '-') address1
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),3), '-') address2
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),2), '-') address3
,coalesce(PARSENAME(REPLACE(_Venue, ',', '.'),1), '-') address4
FROM table

Address1 Address2 Address3 Address4
----------------------------------------------------------------------------------------
Queen Elizabeth's Hunting Lodge Rangers Road Chingford London E4 7 QH
- All Saints' Church Shrub End Road Colchester
- - - Audley End House

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 13:30:37

Didnt get what you're asking for. Do you mean you want to reorder bits in some other order?

mflammia
Starting Member

44 Posts

Posted - 2008-06-05 : 13:49:25

Sorry, a bit more detail.

Have a database of which the _Venue column contains address information of varying lengths as per below:

1) Epping Forest Visitor Centre, Paul's Nursery Road, Loughton, Essex IG10 4AE

2) Damyns Hall Aerodrome, Aveley Road, Upminster

3) St James Church Hall, Tower Road

4) Audley End House

What I think is happening, is as the query moves to a row and steps over each apostrophe it displays what it finds, so in the case of the first row above the last piece of text it passes is 'Essex IG10 4AE' which is the county and postcode. As opposed to row 4 which just has 'Audley End House', which is the address name.

Now 'Essex IG10 4AE' and 'Audley End House' will appear under the same column Address4, which is not what I want as they are different parts of the address and I will always need:

Address1 = Name
Address2 = Street
Address3 = Town
Address4 = County

What would be ideal is if you could do it all in reverse then the order would work out.

Have I made anymore sense? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 14:15:04

You could use a udf like this

CREATE FUNCTION dbo.ParseString
(
@String varchar(8000)
)
RETURNS @RESULT Table
(ID  int,
Value varchar(1000)
)
AS
BEGIN
DECLARE @Val varchar(1000),@Count int

SET @Count=1
WHILE @String IS NOT NULL
BEGIN
SELECT @Val=CASE WHEN CHARINDEX(',',@String) > 0 THEN LEFT(@String,CHARINDEX(',',@String)-1)
            ELSE @String
            END,
@String=CASE WHEN CHARINDEX(',',@String) > 0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String))
            ELSE NULL
            END

INSERT INTO @RESULTS
SELECT @Count,@Val

SET @Count=@Count+1
END

RETURN
END

and use it like this

SELECT t.PKCol,
       MAX(CASE WHEN b.ID = 1 THEN Value ELSE NULL END) AS Address1,
       MAX(CASE WHEN b.ID = 2 THEN Value ELSE NULL END) AS Address2,
       MAX(CASE WHEN b.ID = 3 THEN Value ELSE NULL END) AS Address3,
       MAX(CASE WHEN b.ID = 4 THEN Value ELSE NULL END) AS Address4,
FROM YourTable t
CROSS APPLY dbo.ParseString(t._Venue)b
GROUP BY t.PKCol

Where PKCol is primary key of your table.

mflammia
Starting Member

44 Posts

Posted - 2008-06-05 : 17:59:01

My apologies but this query is a little beyond what I understand, I have tried to add the relevant parts to make it work with my db as per below and get the error messages also below. Sorry, need a little more help. Thanks.

CREATE FUNCTION dbo.ParseString
(
@String varchar(8000)
)
RETURNS @RESULT TABLE
(ID int,
Value varchar(1000)
)
AS
BEGIN
DECLARE @Val varchar(1000),@Count int

SET @Count=1
WHILE @String IS NOT NULL
BEGIN
SELECT @Val=CASE WHEN CHARINDEX(',',@String) > 0 THEN LEFT(@String,CHARINDEX(',',@String)-1)
ELSE @String
END,
@String=CASE WHEN CHARINDEX(',',@String) > 0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String))
ELSE NULL
END

INSERT INTO @RESULTS
SELECT @Count,@Val

SET @Count=@Count+1
END

RETURN
END

SELECT t._ID,
MAX(CASE WHEN b.ID = 1 THEN Value ELSE NULL END) AS Address1,
MAX(CASE WHEN b.ID = 2 THEN Value ELSE NULL END) AS Address2,
MAX(CASE WHEN b.ID = 3 THEN Value ELSE NULL END) AS Address3,
MAX(CASE WHEN b.ID = 4 THEN Value ELSE NULL END) AS Address4,
FROM RealEssex_Events.dbo._DATACAPTURE t
CROSS APPLY dbo.ParseString(t._Venue)b
GROUP BY t._ID

Msg 1087, Level 15, State 2, Procedure ParseString, Line 23
Must declare the table variable "@RESULTS".
Msg 156, Level 15, State 1, Procedure ParseString, Line 33
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure ParseString, Line 38
Incorrect syntax near the keyword 'FROM'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-06 : 09:41:09

Try

SELECT t._ID,
MAX(CASE WHEN b.ID = 1 THEN Value ELSE NULL END) AS Address1,
MAX(CASE WHEN b.ID = 2 THEN Value ELSE NULL END) AS Address2,
MAX(CASE WHEN b.ID = 3 THEN Value ELSE NULL END) AS Address3,
MAX(CASE WHEN b.ID = 4 THEN Value ELSE NULL END) AS Address4
FROM RealEssex_Events.dbo._DATACAPTURE t
CROSS APPLY dbo.ParseString(t._Venue)b
GROUP BY t._ID

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 10:24:12

quote:
Originally posted by mflammia

My apologies but this query is a little beyond what I understand, I have tried to add the relevant parts to make it work with my db as per below and get the error messages also below. Sorry, need a little more help. Thanks.

CREATE FUNCTION dbo.ParseString
(
@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int,
Value varchar(1000)
)
AS
BEGIN
DECLARE @Val varchar(1000),@Count int

SET @Count=1
WHILE @String IS NOT NULL
BEGIN
SELECT @Val=CASE WHEN CHARINDEX(',',@String) > 0 THEN LEFT(@String,CHARINDEX(',',@String)-1)
ELSE @String
END,
@String=CASE WHEN CHARINDEX(',',@String) > 0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String))
ELSE NULL
END

INSERT INTO @RESULTS
SELECT @Count,@Val

SET @Count=@Count+1
END

RETURN
END
GO

SELECT t._ID,
MAX(CASE WHEN b.ID = 1 THEN Value ELSE NULL END) AS Address1,
MAX(CASE WHEN b.ID = 2 THEN Value ELSE NULL END) AS Address2,
MAX(CASE WHEN b.ID = 3 THEN Value ELSE NULL END) AS Address3,
MAX(CASE WHEN b.ID = 4 THEN Value ELSE NULL END) AS Address4,
FROM RealEssex_Events.dbo._DATACAPTURE t
CROSS APPLY dbo.ParseString(t._Venue)b
GROUP BY t._ID

Msg 1087, Level 15, State 2, Procedure ParseString, Line 23
Must declare the table variable "@RESULTS".
Msg 156, Level 15, State 1, Procedure ParseString, Line 33
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Procedure ParseString, Line 38
Incorrect syntax near the keyword 'FROM'.

Sorry there was a typo. Also the UDF needs to created first (till GO) and then run the query below.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-06 : 11:18:56

Also there is extra comma before FROM

Madhivanan

Failing to plan is Planning to fail

mflammia
Starting Member

44 Posts

Posted - 2008-06-06 : 12:34:50

This is a fantanstic forum and really appreicate all the help on this, it worked beautifully! Thanks.

Sorry, one other little question - I wanted to change the Null attribute in column Address4 to a word 'ESSEX'. I tried changing the query as per below, but this writes over everything not just Null - where am I going wrong?

MAX(CASE WHEN b.ID = 4 THEN Value ELSE 'ESSEX' END) AS Address4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 12:39:50

quote:
Originally posted by mflammia

This is a fantanstic forum and really appreicate all the help on this, it worked beautifully! Thanks.

Sorry, one other little question - I wanted to change the Null attribute in column Address4 to a word 'ESSEX'. I tried changing the query as per below, but this writes over everything not just Null - where am I going wrong?

MAX(CASE WHEN b.ID = 4 THEN Value ELSE 'ESSEX' END) AS Address4

SELECT _ID,Address1,Address2,Address3,ISNULL(Address4,'ESSEX')
FROM
(
SELECT t._ID,
MAX(CASE WHEN b.ID = 1 THEN Value ELSE NULL END) AS Address1,
MAX(CASE WHEN b.ID = 2 THEN Value ELSE NULL END) AS Address2,
MAX(CASE WHEN b.ID = 3 THEN Value ELSE NULL END) AS Address3,
MAX(CASE WHEN b.ID = 4 THEN Value ELSE NULL END) AS Address4,
FROM RealEssex_Events.dbo._DATACAPTURE t
CROSS APPLY dbo.ParseString(t._Venue)b
GROUP BY t._ID
)tmp

mflammia
Starting Member

44 Posts

Posted - 2008-06-06 : 13:07:23

Wonderful, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources