RE: Combining DISTINCT and SUBSTRING to dynamically build a query

From: Brad Syputa - MS (bradsy_at_Online.Microsoft.com)
Date: 07/12/04 

Date: Mon, 12 Jul 2004 18:17:31 GMT

Hi Johnny,

It should work for you as you have it described. I do not have any problem
with it at all. Can you give it another try?

1> create table foo (col1 nchar(44), col2 nchar(44));
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('11aa','22aa');
1> insert into foo (col1, col2) values ('22aa','22aa');
1> insert into foo (col1, col2) values ('22aa','22aa');
1> insert into foo (col1, col2) values ('22aa','22aa');
1> insert into foo (col1, col2) values ('22aa','22aa');
1> select substring(col1,1,2) from foo;
                  #0
--------------------
                  11
                  11
                  11
                  11
                  11
                  11
                  22
                  22
                  22
                  22
1> select distinct substring(col1,1,2) from foo;
                  #0
--------------------
                  11
                  22
--------------------
| Reply-To: "Johnny O." <anonymous@discussions.msdn.microsoft.com>
| From: "Johnny O." <anonymous@discussions.msdn.microsoft.com>
| Subject: Combining DISTINCT and SUBSTRING to dynamically build a query
| Date: Sat, 10 Jul 2004 21:29:13 -0700
| Lines: 35
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <#aNj09vZEHA.3480@TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.ce
| NNTP-Posting-Host: gteb79.isomedia.com 207.115.71.79
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.ce:12813
| X-Tomcat-NG: microsoft.public.sqlserver.ce
|
| Is is possible to construct a query SQLCE recognizes which will return a
| list of distinct entries which are a substring of a column's text? I have
a
| series of customer account numbers in column CAccount, such as:
|
| CAccount
| 10-23452
| 10-23456
| 10-23555
| 12-12455
| 12-13444
| 15-22421
|
| I'd like to bind a listbox to a dynamically populated data set which
| includes the distinct entries of the first two characters in each customer
| account--ie, the listbox for the six entries above should appear as:
| 10
| 12
| 15
|
| For the life of me, I can't construct the query. This is the best guess I
| had:
|
| SELECT DISTINCT SUBSTRING(CAccount,1,2) FROM tblCustomers
-OR-
| SELECT DISCTINCT (SUBSTRING(CAccount,1,2)) FROM tblCustomers
|
| but I when I try to run the query in QueryAnalyzer, I get an error (The
| specified argument value for the function is not valid)
|
| Is there no way to dynamically build this query? I suppose I could create
a
| temp table, read the SUBSTRING into the table, and SELECT DISTINCT on it,
| but I'd prefer not to add the complexity, etc.
|
| thx
|
|
|

Relevant Pages