Prev: count unique nodes
Next: Script-Out Full Text Search?
From: Erland Sommarskog on 11 Jun 2010 18:11
larryg003 (u60820(a)uwe) writes:
> I have 10 columns and 8,000,000 rows which have stock ticker names and
> exchange tickers-like the following:
> [Column A]
> AQR.L
> GDY.LS
> NBAE.LS
> (blank)
> 0023CA.S
> FOR.L
> AQR.KZ
> ..
> .. How do I find the number of unique ticker names in my database and
> disregard the .L, .LS, .S, .L, .KZ endings (because I am looking for the
> unique tickers, not the exchange). I.E., I want the values counted to be
> only AQR, GDY, NBAE, 0023CA, FOR or 5 values (I don't want AQR to be
> counted twice because it is listed on two separate exchanges).

Since the separator is dot, you can use the parsename() function:

SELECT coalesce(parsename[Column A], 2), parsename([Column A], 1)),
COUNT(*)
FROM tbl
GROUP BY coalesce(parsename[Column A], 2), parsename([Column A], 1))

Parsename is really meant to parse object names, but hey anything goes.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: count unique nodes
Next: Script-Out Full Text Search?