From: Erland Sommarskog on
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]
> (blank)
> 0023CA.S
> ..
> .. 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)),
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)

Links for SQL Server Books Online:
SQL 2008:
SQL 2005:
SQL 2000:

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