Grouping with LIKE? - 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 2000 Forums

SQL Server Development (2000)

Grouping with LIKE?

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-09-16 : 10:55:45

I am trying to group on a single Row of data, the data is like W788 (This is a Settype field) or W788/3 or W788/7 how can I strip off the "/?" or do a LIKE to accomplish this to get an actual count of my settypes.

Other data may look like M755 or MT325XI all may contain a /? in them or not. I just want to go through or Settype field and see what the actual numbers are. The Settype field contains different lengths of data so none are the same size

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 11:01:32

[code]DECLARE @Sample TABLE
(
Data VARCHAR(200)
)

INSERT @Sample
SELECT 'W788/3' UNION ALL
SELECT 'W788/7' UNION ALL
SELECT 'M755' UNION ALL
SELECT 'MT325XI'

SELECT dta,
COUNT(*) AS Items
FROM (
SELECT CASE CHARINDEX('/', Data)
WHEN 0 THEN Data
ELSE LEFT(Data, CHARINDEX('/', Data) - 1)
END AS dta
FROM @Sample
) AS d
GROUP BY dta
ORDER BY dta[/code]

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

nhaas
Yak Posting Veteran

90 Posts

Posted - 2008-09-19 : 17:39:33

thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources