count occurrence of character in a field using SQL

Is there a function that will enable me to count the number of instances a
particular character is in a feild? For instance...if I have a field named
number with a record with characters such as 00000111100000 and I what to do
a function that tells me the number of times the number 1 shows up in the
number field of that record. The result would be 4. Is this possible?SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
> Is there a function that will enable me to count the number of instances a
> particular character is in a feild? For instance...if I have a field
> named
> number with a record with characters such as 00000111100000 and I what to
> do
> a function that tells me the number of times the number 1 shows up in the
> number field of that record. The result would be 4. Is this possible?|||Perfect! Thank you!
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
>
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
>
>

Could not find xp_trace_setqueryhistory

I'm trying to enable black-box reporting, but I must be doing something wrong:

> exec xp_trace_setqueryhistory

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure 'xp_trace_setqueryhistory'.

Am I missing something obvious?

Thanks!

Hello,

The xp_trace xp's haven't been in mssql since 7.0. You need to use the sp_trace% procs.

Cheers,

Rob

|||Well, that explains why I couldn't find them. Thanks!

Could not find installable ISAM

Hi:

I got an error message when I am trying to have a stored procedure import a table from an excel file. It's a SQL Server 2005 and I have enable the Ad Hoc Distributed Queries option. The query I am testing is as below:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=S:\myfile.xls;Extended Properties=Excel 11')...TEST$

The error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

What could be the problem? Thanks.Hello

I have encountered the same problem while accesing from VB and access, I don't know how with SQL! Still what I did in my case is to run set up of Visual Studio and install the component in section Data Access - Jet IISAM. Try and see if this is of any help to you

Regards
Reshmi

Could not able to enable auto identity range, please help.

Hi there,
I am trying to publish database and replicate using merge replication.
Lot of my table have identity column which are pk as well. I crated
publication and it not giving me option to enable auto identity range
by sql server 2000 itself. could you please let me know what i need to
do so that server give me option to enable auto identity range
management option during initial pulication creation.
Please help me .
Thanks.
Indra.
Have you already created your Publication?
If so you can't do this by altering the publication/articles properties via
the EM.
open up Query Analyzer and do this
declare @.keyword varchar(250)
declare @.keyword2 varchar(250)
declare @.str nvarchar(2500)
set @.keyword='ringer'
set @.keyword2='green'
set @.str='select * from authors as a join containstable(authors,
*,''isabout('+char(34)+@.keyword +char(34)+' weight(.8),'
+char(34)+@.keyword2+char(34)+' weight(.7))'') as t on a.au_id=t.[key]'
exec (@.str)
in front of isabout there are two single quotes, same as by weight(.7))
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Indra" <itimilsina@.savannaenergy.com> wrote in message
news:e667cf1e.0407220804.11855f4b@.posting.google.c om...
> Hi there,
> I am trying to publish database and replicate using merge replication.
> Lot of my table have identity column which are pk as well. I crated
> publication and it not giving me option to enable auto identity range
> by sql server 2000 itself. could you please let me know what i need to
> do so that server give me option to enable auto identity range
> management option during initial pulication creation.
> Please help me .
> Thanks.
> Indra.
|||oops, scratch that - wrong newsgroup
I think you have to drop your publication and recreate it. When you get to
the specify articles dialog, click the browse button and go to the identity
range tab and make the changes there.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23HuVUpBcEHA.2388@.TK2MSFTNGP11.phx.gbl...
> Have you already created your Publication?
> If so you can't do this by altering the publication/articles properties
via
> the EM.
> open up Query Analyzer and do this
> declare @.keyword varchar(250)
> declare @.keyword2 varchar(250)
> declare @.str nvarchar(2500)
> set @.keyword='ringer'
> set @.keyword2='green'
> set @.str='select * from authors as a join containstable(authors,
> *,''isabout('+char(34)+@.keyword +char(34)+' weight(.8),'
> +char(34)+@.keyword2+char(34)+' weight(.7))'') as t on a.au_id=t.[key]'
> exec (@.str)
> in front of isabout there are two single quotes, same as by weight(.7))
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Indra" <itimilsina@.savannaenergy.com> wrote in message
> news:e667cf1e.0407220804.11855f4b@.posting.google.c om...
>