Need Help - 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

Need Help

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-13 : 06:12:36

in my table description column contained values like this

Marketing Manager
HR Executive
Front Office Executive
Content Writer
Administrator
Testing Engineer
Software Trainee Trainer

i want out put like this

MM
HE
FOE
CW
A
TE
STT

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-13 : 06:44:58

Hi,

I want addrivation for that column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 07:10:58

its better to create a mapping table with this abbreviation and use it in queries by joining to main table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 07:20:53

Here is a start for you, until you write which version of SQL Server you use.

DECLARE	@Sample TABLE
	(
		data VARCHAR(200)
	)

INSERT	@Sample
SELECT	'Marketing Manager' UNION ALL
SELECT	'HR Executive' UNION ALL
SELECT	'Front Office Executive' UNION ALL
SELECT	'Content Writer' UNION ALL
SELECT	'Administrator' UNION ALL
SELECT	'Testing Engineer' UNION ALL
SELECT	'Software Trainee Trainer'

SELECT		s.data,
		SUBSTRING(s.data, v.number, 1)
FROM		@Sample AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			and v.number > 0
			and v.number <= len(s.data)
WHERE		substring(' ' + s.data, v.number, 1) = ' '

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 07:26:58

This will work for up to 3 words:

SELECT
Case
--No space so just 1st letter
when CHARINDEX(' ', textString) = 0 then left(textString,1)
--No second space so just 2 words
when CHARINDEX(' ', textString,CHARINDEX(' ', textString)+1)= 0
then
left(textString,1) + SUBSTRING(textString, CHARINDEX(' ', textString) + 1, 1)
else
--3 words
left(textString,1) + SUBSTRING(textString, CHARINDEX(' ', textString) + 1, 1) +
--reverse string to get last word
Left(Right(textString,CHARINDEX(' ', Reverse(textString))-1),1)

end
FROM TableName

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-13 : 08:15:42

Thank You

svicky9
Posting Yak Master

232 Posts

Posted - 2009-01-13 : 08:21:21

use a lookup table and reference it via a function

if the table is table1

Designation Code
MANAGER MGM
ADMINISTRATOR ADM

the function will be

create function dbo.getCode(@Designation)

as

declare @code varchar(10)

select @code = code from table1 where designation like @Designation

return @code

--Result

select dbo.getCode('Manager')

--MGM

You may need to declare the paramter in the function

Vic

http://vicdba.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources