string functions - 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 functions

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-07-07 : 08:06:49

i have following code:


declare @temp table
(some_text  varchar(30))

insert into @temp values ('Dexters Laboratory DeeDee')
insert into @temp values ('Dexter Boy Genius')
insert into @temp values ('DexterBoy')



select
	some_text
	,left(some_text,2) as text2
	,(len(rtrim(some_text)) - len(replace(rtrim(some_text), ' ', ''))) as Nof_spacebars
	,case when (len(rtrim(some_text)) - len(replace(rtrim(some_text), ' ', '')) = 0)
					then left(some_text,2) 
					else left(some_text,4)  end as text3

from @temp

and i get the output:
---
some_text text2 Nof_spacebars text3
------------------------------ ----- ------------- -----
Dexters Laboratory DeeDee De 2 Dext
Dexter Boy Genius De 2 Dext
DexterBoy De 0 De
---

but i would like in coulumn text3 following output; when there are two or more words i would like only the first initial of first two words.

so i would get for text3 results:
text3
----
DL
DB
De

any Ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 08:12:27

[code]SELECT CASE WHEN CHARINDEX(' ', some_text) > 0 THEN
left(some_text, 1) + SUBSTRING(some_text, CHARINDEX(' ', some_text) + 1, 1)
ELSE
left(some_text, 2)
END
FROM @temp[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources