Need Query. - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query.

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-02-20 : 10:04:15

Hi,

I have The following records in table.

Best Buy
Del Systems
Global Positioning System
........
.......

I need the output like
BB
DS
GPS
...
...

(Some times it may be 4 to 5 words)
Can any one help me to do this..?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-20 : 10:43:01

[code]-- Prepare sample data
DECLARE @Sample TABLE (Data VARCHAR(200))

INSERT @Sample
SELECT 'Best Buy' UNION ALL
SELECT 'Del Systems' UNION ALL
SELECT 'Global Positioning System'

-- Setup CTE
;WITH Yak (Data, Position, Letter)
AS (
SELECT s.Data,
n.Number,
SUBSTRING(s.Data, n.Number, 1) AS c
FROM @Sample AS s
CROSS APPLY (
SELECT Number
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 1 AND LEN(Data)
) AS n
WHERE SUBSTRING(' ' + s.Data, n.Number, 1) = ' '
)

-- Show the expected output
SELECT DISTINCT y.Data,
(SELECT TOP 100 PERCENT '' + x.Letter FROM Yak AS x WHERE x.Data = y.Data ORDER BY x.Position FOR XML PATH('')) AS Abbrev
FROM Yak AS y
ORDER BY y.Data[/code]

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

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-02-20 : 11:18:44

Thank you peso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources