Strange result using Parsname Query

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

Strange result using Parsname Query

Author

Topic

mflammia
Starting Member

44 Posts

Posted - 2008-07-17 : 16:58:32

I am a newbie so maybe the answer is simple?

Have devised the query below from the query I already had below that:

SELECT coalesce(PARSENAME(REPLACE(_Address, ',', '.'),6), '-') address1
,coalesce(PARSENAME(REPLACE(_Address, ',', '.'),5), '-') address2
,coalesce(PARSENAME(REPLACE(_Address, ',', '.'),4), '-') address3
,coalesce(PARSENAME(REPLACE(_Address, ',', '.'),3), '-') address4
,coalesce(PARSENAME(REPLACE(_Address, ',', '.'),2), '-') address5
,coalesce(PARSENAME(REPLACE(_Address, ',', '.'),1), '-') address6
FROM DBname.dbo._DATA

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 DBname_Events.dbo._DATACAPTURE

Unable to explain why but the first query will not work with any address which has a delimiter of more than four times.

For example the DB entries below:

Name1, CO1 1DN
Name1, Name2, CM1 1JE
Name1, Name2, Name3, Name4, CO1 1PT
Name1, Name2, Name3, Name4, Name, CO5 7DN

Give the following results:

- - - - Name1 C01 1DN
- - Name1 Name2 Name3 CM1 1JE
- - - - - - -
- - - - - - -

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 17:01:38

Parsename only supports four parts.
Search for fnParseString function to be able to split more parts.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 17:08:03

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
Use last version of fnParseString.

E 12°55'05.25"
N 56°04'39.16"

mflammia
Starting Member

44 Posts

Posted - 2008-07-17 : 17:28:13

Thanks for the post. Have looked at the link but think this is perhaps way beyond my limited knowledge. I'll have a crack trying to understand though:

NULLIF(dbo.fnParseString(6, ',', '.'),6), '-') address1
NULLIF(dbo.fnParseString(6, ',', '.'),5), '-') address2
NULLIF(dbo.fnParseString(6, ',', '.'),4), '-') address3
NULLIF(dbo.fnParseString(6, ',', '.'),3), '-') address4
NULLIF(dbo.fnParseString(6, ',', '.'),2), '-') address5
NULLIF(dbo.fnParseString(6, ',', '.'),1), '-') address6

Am I close?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 17:30:28

Start out with using only dbo.fnParseString(6, ',', Col1)
Then apply other formatting function later.

E 12°55'05.25"
N 56°04'39.16"

mflammia
Starting Member

44 Posts

Posted - 2008-07-17 : 17:46:46

Sorry, this is all really new to me - hope its Ok for me to keep posting as I learn?

Tried:

Create Function dbo.fnParseString(6, ',', _Address)

Got error:

Msg 102, Level 15, State 1, Procedure fnParseString, Line 1
Incorrect syntax near '6'.

Assumed I don't use the Select command.

Not sure how I compile this at all into the complete Query of getting the result I want.

Thanks for your patience.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 17:50:13

Copy this code to a new query window (connected to the database in question)

CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@NextPos SMALLINT,
		@LastPos SMALLINT,
		@Found SMALLINT

	IF @Section > 0
		SELECT	@Text = REVERSE(@Text)

	SELECT	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
		@LastPos = 0,
		@Found = 1

	WHILE @NextPos > 0 AND ABS(@Section) <> @Found
		SELECT	@LastPos = @NextPos,
			@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1),
			@Found = @Found + 1

	RETURN	CASE
			WHEN @Found <> ABS(@Section) OR @Section = 0 THEN NULL
			WHEN @Section > 0 THEN REVERSE(SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END))
			ELSE SUBSTRING(@Text, @LastPos + 1, CASE WHEN @NextPos = 0 THEN DATALENGTH(@Text) - @LastPos ELSE @NextPos - @LastPos - 1 END)
		END
END

Then execute the code.
Now you have created a function named dbo.fnParseString in your database.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 17:53:03

Then run this query to get the addresses

SELECT	dbo.fnParseString(-1, ',', _Address) AS Address1,
	dbo.fnParseString(-2, ',', _Address) AS Address2,
	dbo.fnParseString(-3, ',', _Address) AS Address3,
	dbo.fnParseString(-4, ',', _Address) AS Address4,
	dbo.fnParseString(-5, ',', _Address) AS Address5,
	dbo.fnParseString(-6, ',', _Address) AS Address6
FROM	DBname.dbo._DATA

E 12°55'05.25"
N 56°04'39.16"

mflammia
Starting Member

44 Posts

Posted - 2008-07-17 : 17:57:39

Well that worked wonderfully, thanks very much! Would never of quessed that code in a million years, have a long way to go to grasp this.

Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 18:11:33

Notice that my function takes both negative and positive position arguments.
If you use positive position arguments, you count from right to left (as PARSENAME does).
If you use negative position arguments, you count from left to right.

E 12°55'05.25"
N 56°04'39.16"

mflammia
Starting Member

44 Posts

Posted - 2008-07-17 : 18:11:43

Sorry, being a bit of a nuisance here I know. There is one little issue - the results are returned with the address parts under different columns, for example:

Name1 CO1 1DN - - - -
Name1 Name2 C011DN - - -
Name1 Name2 Name3 CM11JE - -
Name1 Name2 Name3 Name4 C011YH -

I need to reference everything in the same order apart from the postcode which always needs to appear in the last column - how would I do that?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-05 : 16:40:59

Positive parameter to the function is counted right-to-left.
Negative parameter to the function is counted left-to-right.

E 12°55'05.63"
N 56°04'39.26"

mflammia
Starting Member

44 Posts

Posted - 2008-10-05 : 17:04:54

Arr, yes but that does not help me.

Basically I use a macro to copy the address information from the DB into a form. The address information requires Name, street, Town, County then postcode in that order.

Actually thinking about it, I would always need the right part of the address to always appear in the right column - that I imagine, is going to be a problem.

Suppose what would work best for what I need, is to always have the last two parts of the address always appear in the last two columns and everything else start from left to right.

Is that’s possible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources