Create a string with carriage return and line feed

Hello,
Is it possible to create a variable that has carriage return and line feed
embed inside. Say I have 3 fields: field1, field2, field3. I want to return
a
string with carriage returns and line feeds between field1, 2 and field2, 3.
Thanks in advanceLOOK in BOL for CHAR,
TAB Char(9)
LineFeed Char(10)
CarriageReturn Char(13)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Ed Chiu" <EdChiu@.discussions.microsoft.com> schrieb im Newsbeitrag
news:462D3FCC-28E9-4660-A5F7-559D5CB3CA33@.microsoft.com...
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
> return a
> string with carriage returns and line feeds between field1, 2 and field2,
> 3.
> Thanks in advance|||> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
return a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
Include CHAR(13)+CHAR(10) when you are concatenating the strings.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Try,
select 'Microsoft' + char(13) + char(10) + 'SQL' + char(13) + char(10) +
'Server' + char(13) + char(10) + '2000'
AMB
"Ed Chiu" wrote:

> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advance|||DECLARE @.var VARCHAR(50)
SET @.var = 'joe' + CHAR(13) + CHAR(10) + 'blow'
SELECT @.var
It's up to the consumer of the data to display it properly. For example
in Query Analyzer, if you choose to display results in a grid, it
replaces the carriage return, line feed with spaces. If you choose to
display in text mode, it'll put the carriage return, line feed in there
for you.
If you're returning this to a .net dataset, you can display it properly.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Ed Chiu wrote:
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advancesql

Create a string of records from a table in a stored procedure,

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401190748.491c6219@.posting.google.c om...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

Generally the best way to do this would be in a front end application, where
it's easier to handle string manipulation. But this thread may be useful if
you have no other choice than to do it in MSSQL:

http://tinyurl.com/bib2

Simon|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@.CustId as int) returns
varchar(100)
as
begin
declare @.ret_value varchar(100)
SET @.ret_value=''
Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
CustID=@.CustId
RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer|||Amit Gupta (amiiit@.hotmail.com) writes:
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end

Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Amit,
Thank you for your examples. I realize there is still a lot to learn
for with the SQL. I have never used "Create function" and don't know
where it goes. Sorry for the ignorance...
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||Specifically...
When I try to cretae a stored procedure containing this Create
Function, I get the error:
"You cannot chnage the object type in a script."
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||In the DAH! Department...
I realize I have to do the Create Function in Enterprise Manager. I've
been using Microsoft Access MSDE as the front end development tool...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947725BA51CYazorman@.127.0.0.1>...
> Amit Gupta (amiiit@.hotmail.com) writes:
> > /*Create a function to do the job*/
> > Create function dbo.fn_concatinate(@.CustId as int) returns
> > varchar(100)
> > as
> > begin
> > declare @.ret_value varchar(100)
> > SET @.ret_value=''
> > Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> > CustID=@.CustId
> > RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> > end
> Not that this function relies on undefined behaviour. It may return
> the expected result, or it may return something else. See
> http://support.microsoft.com/default.aspx?scid=287515.

Create a string from report parameters, return checksum

Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>

Create a SP that will launch a DTS package

Hi all. How can I create a Stored Procedure that will launch a DTS package that I have already built dynamically ?

I have a string already built and I need to put it in a SP because my application is php (linux) and the database is SQL Server on a Windows machine.

I will use php to execute the stored proc, which is the only way to access it.

My string looks like this :
C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S [MyServer] -U [Username] -P [Password] -Q "ISQL_Batch 'D:\DDFIImporte\IMPICAFI.bat [user] [Schema] [Pwd] '" -n -d [database]

(Words in [] are only to show that I will put other values)

Thanks

CFGillesxp_cmdshell but it will have to execute with sysadmin permissions.|||Hi,

Can you be more specific ?? I'm a near-newby in SP building... Maybe some pseudo-code, some links or examples ?

Thanks

CFGillessql

CR9 - Link String field to Number Field

Hey,

I have two tables used within a report one therough btrieve and the other odbc.

Within each table there is a field called {emp.no}. However, one field is located within a Jobshop database and is a string field and the other is located within a TimeLOG database and is a number field.

Is there anyway of linking these fieds within crystal? I dont belive its possible with the Database Expert. But maybe elsewhere?

The report feeds through from the Jobshop (string) field and I need to match records within the TimeLOG (number) database to those within Jobshop based on emp.no and a date field.

Any help much appreciated.

Regards

RobSee the help on the ToText and ToNumber functions.
You should be able to use these in the Record Selection formula.

CR Ver 10 String to Memo Field

How can I convert a String Field to a Memo Field so I can display more then 255 characters on my report? I've tried the "Can Grow" option and it's not working.......After setting the can grow option, did you increase the field height?

covert from string to integer

Are there any function to conver a string to integer?
like it can convert a string "16" to a number 16=CInt("16") should work
or
=Int32.Parse("16")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eE3Z0DQnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Are there any function to conver a string to integer?
> like it can convert a string "16" to a number 16
>

Counting the occurence of a string ...

Hi ...
I have a weblog database where I want to count the occurences of a
table of string values that appear in all the urls viewed.

My tblWebLog as a field that contains the url ...
tblWebLog.[cs-uri-stem]

I have another table ... tblStrings ... that has a field [strSearch]
for a string value and an integer field [intViewCount] to count the
occurence of the string in tblWebLog.[cs-uri-stem]

I've been trying ...

Update tblStrings
Set [intViewCount] = (Select Count(*) From tblWebLog Where
[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')

... but it doesn't fly and I'm stumped. Any thoughts?

Cheers.DaFerg (dave@.myalarm.com) writes:

Quote:

Originally Posted by

I have a weblog database where I want to count the occurences of a
table of string values that appear in all the urls viewed.
>
My tblWebLog as a field that contains the url ...
tblWebLog.[cs-uri-stem]
>
I have another table ... tblStrings ... that has a field [strSearch]
for a string value and an integer field [intViewCount] to count the
occurence of the string in tblWebLog.[cs-uri-stem]
>
I've been trying ...
>
Update tblStrings
Set [intViewCount] = (Select Count(*) From tblWebLog Where
[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')
>
... but it doesn't fly and I'm stumped. Any thoughts?

Your query make sense in relation to your description, but then again I
may be misunderstanding something.

For this sort of question, it helps if you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This helps to clarify what you are looking for, and it makes it possible
to copy and paste and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If tblStrings.[strSearch] is defined as fixed length (char) instead of
varying length (varchar) that would cause problems. Or if trailing
blanks were stored in a varchar column. Try using
RTRIM(tblStrings.[strSearch]) instead.

Roy Harvey
Beacon Falls, CT

On 27 Aug 2006 01:00:04 -0700, "DaFerg" <dave@.myalarm.comwrote:

Quote:

Originally Posted by

>Hi ...
>I have a weblog database where I want to count the occurences of a
>table of string values that appear in all the urls viewed.
>
>My tblWebLog as a field that contains the url ...
>tblWebLog.[cs-uri-stem]
>
>I have another table ... tblStrings ... that has a field [strSearch]
>for a string value and an integer field [intViewCount] to count the
>occurence of the string in tblWebLog.[cs-uri-stem]
>
>I've been trying ...
>
>Update tblStrings
>Set [intViewCount] = (Select Count(*) From tblWebLog Where
>[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%')
>
>... but it doesn't fly and I'm stumped. Any thoughts?
>
>Cheers.

Counting the occurance of an alphabet in a String

Hi,
I want to count the occurance of an alphabet in a String For example
If the string is "APPLE"
If I want to count the occurance of the letter 'P' in "Apple" the result should be 2
How do I do this.
ThanksThere might be a more efficient way, but what I have done is this:

SELECT
 LEN(myColumn) - LEN(REPLACE(myColumn,'P','')) AS NumberOfOccurrences
FROM
 myTable

Terri