substring questioin - 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

substring questioin

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-15 : 11:27:38

declare @bid table(
streetNumber varchar(10),
streetName varchar(100),
streetNameSuffix varchar(100)
)

insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23'

select * from @bid

update @bid
set streetNumber = case
when substring(streetName,1,charindex(' ',streetName)) like '[1-9]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9]'
then substring(streetName,1,charindex(' ',streetName))
else null
end,

streetNameSuffix = case
when substring(streetName,1,charindex(' ',streetName)) like '[1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9][a-z]'
then substring(streetName, 1, charindex(' ', streetName))
else null
end,

--streetName = substring(streetName, charindex(' ',streetName) + 1, len(streetName)-charindex(' ',reverse(streetName))-charindex(' ', streetName)) + ' ' +
-- substring(streetName,len(streetName)-charindex(' ',reverse(streetName))+2,len(streetName))

streetName = substring(streetName, charindex(' ', streetName)+1, len(streetName)-len(charindex(' ', streetName)))

select * from @bid

how can i write my code in order for the "POB 23" to stay the in streetName field?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-15 : 13:14:17

you were not applying your conditional logic to the streetname update - you were ALWAYS updating streetname. The following WORKS - I won't speak for performance:


SET NOCOUNT on
 declare @bid table(
streetNumber varchar(10),
streetName varchar(100),
streetNameSuffix varchar(100)
)

insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23'

select * from @bid

update @bid
set streetNumber = case
WHEN	substring(streetName,1,charindex(' ',streetName)) like '[1-9]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9]'
THEN	substring(streetName,1,charindex(' ',streetName))
ELSE	null
end,

streetNameSuffix = case
when substring(streetName,1,charindex(' ',streetName)) like '[1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][a-z]' OR
substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9][a-z]'
then substring(streetName, 1, charindex(' ', streetName))
else null
end,

--streetName = substring(streetName, charindex(' ',streetName) + 1, len(streetName)-charindex(' ',reverse(streetName))-charindex(' ', streetName)) + ' ' +
-- substring(streetName,len(streetName)-charindex(' ',reverse(streetName))+2,len(streetName))

streetName = 
case
WHEN	substring(streetName,1,charindex(' ',streetName)) like '[1-9]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9]' OR 
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][a-z]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][a-z]' OR
		substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9][a-z]'

THEN	substring(streetName, charindex(' ', streetName)+1, len(streetName)-len(charindex(' ', streetName)))
ELSE	streetName
end


select * from @bid

results


streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
NULL         12 union street                                                                                      NULL
NULL         1A apple avenue                                                                                      NULL
NULL         11A apple st avenue road                                                                             NULL
NULL         POB 23                                                                                               NULL

streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
12           union street                                                                                         NULL
NULL         apple avenue                                                                                         1A 
NULL         apple st avenue road                                                                                 11A 
NULL         POB 23                                                                                               NULL

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-15 : 13:39:40

[code]UPDATE @bid
SET StreetNumber =
CASE
-- Only attempt to match strings up to 4 characters long
WHEN CHARINDEX(' ', StreetName) < 5
-- Replicate '[1-9]' in search string n times where n = length of first element of street name
AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[1-9]', CHARINDEX(' ', StreetName)-1)
THEN LEFT(StreetName,CHARINDEX(' ',StreetName)-1) ELSE NULL
END ,
StreetNameSuffix =
CASE
-- Only attempt to match strings up to 5 characters long
WHEN CHARINDEX(' ', StreetName) < 6
-- Replicate '[1-9]' in search string n times where n = (length of first element of street name) - 1
-- Add [a-z] to match only those elements that end in a non-numeric character
AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[1-9]', CHARINDEX(' ', StreetName)-2) + '[a-z]'
THEN LEFT(StreetName,CHARINDEX(' ',StreetName)-1)
END
[/code]
I don't think I've mangled the logic in the process...

Mark

gongxia649
So Suave

344 Posts

Posted - 2006-08-15 : 14:18:53

mwjdavidson, your code won't work if i insert the following addresses.

insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23' union all
select '0 Landry Street' union all
select 'None'

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-15 : 15:13:23

Erm.. what's it supposed to do in that instance? It replicates the logic of your case statement (i.e. StreetNumber and StreetNameSuffix will be NULL).

Mark

gongxia649
So Suave

344 Posts

Posted - 2006-08-15 : 16:21:26

it won't work if the address is 'NONE'

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-16 : 04:01:39

See my previous response. In your update of streetname would you really want to truncate this to 'NON'?

Mark

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-16 : 05:56:29

quote:

Originally posted by mwjdavidson

UPDATE @bid
SET StreetNumber =
CASE
    -- Only attempt to match strings up to 4 characters long
    WHEN CHARINDEX(' ', StreetName) < 5
    -- Replicate '[1-9]' in search string n times where n = length of first element of street name
        AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[1-9]', CHARINDEX(' ', StreetName)-1)
    THEN LEFT(StreetName,CHARINDEX(' ',StreetName)-1) ELSE NULL 
END ,
StreetNameSuffix = 
CASE
    -- Only attempt to match strings up to 5 characters long
    WHEN CHARINDEX(' ', StreetName) < 6
    -- Replicate '[1-9]' in search string n times where n = (length of first element of street name) - 1
    -- Add [a-z] to match only those elements that end in a non-numeric character
        AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[1-9]', CHARINDEX(' ', StreetName)-2) + '[a-z]'
    THEN LEFT(StreetName,CHARINDEX(' ',StreetName)-1) 
END

I don't think I've mangled the logic in the process...

Mark

While I like the use of replicate, you aren't updating the StreetName column, Mark. That was, afaik, the original requirement.

@Gong - does my solution work for you atm? I've tested it witht the NONE, and it's still fine.

I'll post an updated version using replicate just now, i think

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-16 : 06:04:12

new code using the replciate. Works for current set of test data, imho.


SET NOCOUNT on
 declare @bid table(
streetNumber varchar(10),
streetName varchar(100),
streetNameSuffix varchar(100)
)

insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23' union all
select '0 Landry Street' union all
select 'None' 

select * from @bid

update @bid
set streetNumber = case
WHEN		(CHARINDEX(' ', StreetName) < 5) 
		AND (CHARINDEX(' ', StreetName) <> 0)
		AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-1)
THEN	substring(streetName,1,charindex(' ',streetName))
ELSE	null
end,
streetNameSuffix = case
WHEN		(CHARINDEX(' ', StreetName) < 5) 
		AND (CHARINDEX(' ', StreetName) <> 0)
		AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-2) + '[a-z]'
THEN	substring(streetName, 1, charindex(' ', streetName))
ELSE	null
end,
streetName = 
case
WHEN	(CHARINDEX(' ', StreetName) < 5) 
		AND (CHARINDEX(' ', StreetName) <> 0)
		AND	(	(LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-1))
			OR	(LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-2) + '[a-z]'))
THEN	substring(streetName, charindex(' ', streetName)+1, len(streetName)-len(charindex(' ', streetName)))
ELSE	streetName
end

select * from @bid

results:


streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
NULL         12 union street                                                                                      NULL
NULL         1A apple avenue                                                                                      NULL
NULL         11A apple st avenue road                                                                             NULL
NULL         POB 23                                                                                               NULL
NULL         0 Landry Street                                                                                      NULL
NULL         None                                                                                                 NULL

streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
12           union street                                                                                         NULL
NULL         apple avenue                                                                                         1A 
NULL         apple st avenue road                                                                                 11A 
NULL         POB 23                                                                                               NULL
0            Landry Street                                                                                        NULL
NULL         None                                                                                                 NULL

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-16 : 07:41:55

Wanderer, I've just realised that I missed my introductory preamble off my original post (I typed it up once and then lost my extranet connection) that made clear that I was purposely ignoring the update as you'd already met the original requirement! Sorry for the confusion.

Mark

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-16 : 07:54:20

quote:
Originally posted by mwjdavidson

Wanderer, I've just realised that I missed my introductory preamble off my original post (I typed it up once and then lost my extranet connection) that made clear that I was purposely ignoring the update as you'd already met the original requirement! Sorry for the confusion.

Mark

NP Mark,

I've adjusted to what I think is a relatively reasonable solution, using your replicate idea REALLY neatens up that code!

About the only possible exception I can think ouf would be when the StreetName Suffic has multiple characters: eg:

11ab Street

I am pondering approaching this deifferntly to make it more robust and cater for that situation if it is possible.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

gongxia649
So Suave

344 Posts

Posted - 2006-08-16 : 08:04:00

i like your code using the replicate function.

now, ill try to read and understand your code.

thank you guys again.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-16 : 08:29:51

well, an even more robust version that caters for '11ab Road':


SET NOCOUNT on
 declare @bid table(
streetNumber varchar(10),
streetName varchar(100),
streetNameSuffix varchar(10)
)

insert into @bid (streetName)
select '12 union street' union all
select '1A apple avenue' union all
select '11A apple st avenue road' union all
select 'POB 23' union all
select '0 Landry Street' union all
select 'None' UNION ALL 
SELECT '11ab BadBoy Road' union all
select '1A apple 1A avenue' 


select * from @bid
DECLARE @StreetNumber varchar(10),@streetNameSuffix varchar(10)

update @bid
SET	streetNumber =
	case
	WHEN		(CHARINDEX(' ', StreetName) < 10) 
			AND (CHARINDEX(' ', StreetName) <> 0)
			--creates a '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' mask to compare against
			AND LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-1) 
	THEN	substring(streetName,1,charindex(' ',streetName))
	ELSE	null
	END,
	streetNameSuffix =
	case
	WHEN		(CHARINDEX(' ', StreetName) < 10) 
			AND (CHARINDEX(' ', StreetName) <> 0) 
			--if the substring starts in [0-9] and ends in [a-z]
			AND (LEFT(StreetName, CHARINDEX(' ', StreetName)-1)) LIKE '[0-9]%'
			AND (LEFT(StreetName, CHARINDEX(' ', StreetName)-1)) LIKE '%[a-z]'
	THEN	substring(streetName, 1, charindex(' ', streetName))
	ELSE	null
	END,
	streetName = 
	case
	WHEN	(CHARINDEX(' ', StreetName) < 10) 
			AND (CHARINDEX(' ', StreetName) <> 0)
			AND	(	--creates a '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' mask to compare against
					(LEFT(StreetName, CHARINDEX(' ', StreetName)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', StreetName)-1))
					--if the substring starts in [0-9] and ends in [a-z]
				OR	(	(LEFT(StreetName, CHARINDEX(' ', StreetName)-1)) LIKE '[0-9]%' 
						AND (LEFT(StreetName, CHARINDEX(' ', StreetName)-1)) LIKE '%[a-z]')
				)
	THEN	substring(streetName, charindex(' ', streetName)+1, len(streetName)-len(charindex(' ', streetName)))
	ELSE	streetName
end
select * from @bid

results:


streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------
NULL         12 union street                                                                                      NULL
NULL         1A apple avenue                                                                                      NULL
NULL         11A apple st avenue road                                                                             NULL
NULL         POB 23                                                                                               NULL
NULL         0 Landry Street                                                                                      NULL
NULL         None                                                                                                 NULL
NULL         11ab BadBoy Road                                                                                     NULL
NULL         1A apple 1A avenue                                                                                   NULL

streetNumber streetName                                                                                           streetNameSuffix
------------ ---------------------------------------------------------------------------------------------------- ----------------
12           union street                                                                                         NULL
NULL         apple avenue                                                                                         1A 
NULL         apple st avenue road                                                                                 11A 
NULL         POB 23                                                                                               NULL
0            Landry Street                                                                                        NULL
NULL         None                                                                                                 NULL
NULL         BadBoy Road                                                                                          11ab 
NULL         apple 1A avenue                                                                                      1A

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources