string function #2 - 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

string function #2

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-07-07 : 14:13:25

i have following code


declare @temp table
(address varchar(30))


insert into @temp values ('High street 34')
insert into @temp values ('High London street 32/a')
insert into @temp values ('Highlands 23')
insert into @temp values ('st. Long 34')
insert into @temp values ('st. the Long 34')


select
	address
	,left(replace(address, 'st. the', ''), 1) + SUBSTRING(replace(address, 'st. the', ''), 
CHARINDEX(' ', replace(address, 'st. the', ''))+1, 1) as init

from @temp

and i get results:


address                        init
------------------------------ ----
High street 34                 Hs
High London street 32/a        HL
Highlands 23                   H2
st. Long 34                    sL
st. the Long 34                L

but init should be:

init
-----
Hs
HL
Hi
Lo
Lo

so three rules:
- when 2 space in address or more use initials from first two words ,
- when 1 space in address, use first two letters from first word and
- when address begins with either "st." or "st. the" or "the", skip these words and use initials from two words if more words or only first two letters if one word.

Thank you all :)

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-07-07 : 16:02:58

here is the solution, but i'm sure, there is even more elegant solution:


declare @temp table
(address varchar(30))


insert into @temp values ('High street 34')
insert into @temp values ('High London street 32/a')
insert into @temp values ('Highlands 23')
insert into @temp values ('st. Long 34')
insert into @temp values ('st. The Long 34')

select
	address
	,case when ((len(rtrim(address)) - len(replace(rtrim(address), ' ', ''))) > 1 and not (address like 'st.%' or address like 'st. The%'))
	        then LTRIM(left(address, 1) + SUBSTRING(address, CHARINDEX(' ', address)+1, 1))
	 when 
		((len(rtrim(address)) - len(replace(rtrim(address), ' ', ''))) >= 2 and  (address like 'st. The%')) 
			then
    left(replace(address, 'st. The', ''), 1) + SUBSTRING(replace(address, 'st. The', ''), CHARINDEX(' ', replace(address, 'st. The', ''))+1, 2)
	 when 
		((len(rtrim(address)) - len(replace(rtrim(address), ' ', ''))) >= 2 and  (address like 'st.%')) 
			then
    left(replace(address, 'st.', ''), 1) + SUBSTRING(replace(address, 'st.', ''), CHARINDEX(' ', replace(address, 'st.', ''))+1, 2)

	when (len(rtrim(address)) - len(replace(rtrim(address), ' ', ''))) = 1 then left(address, 2) 
	end as init
from @temp

TalMcMahon
Starting Member

4 Posts

Posted - 2008-07-07 : 16:27:28

Not to make a contoversy, but Why would you do this in your SQL code?

you may want to address this in your business layer not your database.

Tal McMahon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources