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.
| Author |
Topic |
|
larryg003
Starting Member
18 Posts |
 Posted - 2010-06-11 : 09:17:05
|
| Hi guys, i just joined SQLteam.com and I am really impressed with the community.Now on to my question:I have 10 columns and 8,000,000 rows which have stock ticker names and exchange tickers-like the following:[Column A]AQR.LGDY.LSNBAE.LS0023CA.SFOR.LAQR.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). Thank you so much, |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-11 : 09:25:52
|
The query can take a while...selectleft([Column A],charindex('.',[Column A])-1),count(*) as cntfrom your_tablegroup by left([Column A],charindex('.',[Column A])-1)Assuming that in every entry is a dot like shown in your sample data.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
larryg003
Starting Member
18 Posts |
Posted - 2010-06-11 : 12:18:55
|
| Thank you for your suggestion.I tried the Query, but it did not seem to work. It says that there is an error occurring near the word as. Kinda weird.I am using SQL management studio and I forgot to mention that I also have Gaps in the Data, so some of the data may be like this:[Column A] ASD.L(blank)DJS.LSAny suggestions? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-06-11 : 12:22:44
|
| where col1 is not null |
|
|
|
|
|
|
|
|
Data Mining Suggestions?