is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanxYes. CREATE INDEX.
-PatP|||Yes. CREATE INDEX.
-PatP
excuse me while I climb back on my barstool...um office chair...
LNHockey...seriously though... alittle more background on what you're trying to do...
"create index"....why I outta.....|||Yes ok..excuse me.
i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>
and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager
but i want to do it in a store proc..
or how can i modify that value after i add the new column to my table|||Yup...I'm lost..
Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!
just wondering if we can do the same thing in a Store proc using a function kind of thing
thanx !
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Create a user defined function in a user agnostic way
All,
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.
> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.
> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.
> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.
> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)
> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)
> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.
> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).
> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert
I have two functions where one calls the other:
-- simplified example
create function plus(@.a as integer, @.b as integer)
returns integer
begin
return @.a + @.b
end
go
create function t(@.a as integer)
returns integer
begin
return cr_user.plus(10, @.a)
end
go
Is there any way to create those without explicitely mentioning the
database user ("cr_user" in this case)? Ideally I would like to put them
into a file and execute it with osql and those functions are created for
the current user. Until now I could not find a working placeholder to use
in "t" that will call the current user's "plus". (SUSER_SNAME does not
work.)
The only alternative seems to be ATM to always create functions for dbo
which is always defined.
Any ideas? TIA
Kind regards
robertWhy would you need functions that are owned by specific users? Does the
purpose of these functions depend on individual users?
Make it a practice to always qualify object names with the owner.
ML|||ML wrote:
> Why would you need functions that are owned by specific users?
You can only use functions with the owner as prefix and other parts of the
software insert the user as prefix when generating queries.
> Does
> the purpose of these functions depend on individual users?
No, other than that it is only used by a single user.
> Make it a practice to always qualify object names with the owner.
Why? To avoid name clashes? I prefer the unqualified version. It avoids
a lot of hassle and we don't have to take care of name clashes.
Regards
robert|||"Name clashes"?
There are no name clashes if common objects have the default owner (dbo). If
you created these obviously common functions with a qualified name (using db
o
as the owner), you could prevent any problems.
The pros of using qualified names are simply too numerous to mention here.
In SQL 2005 the owner is replaced by a more powerful quaifier - the schema -
maybe you should look into it to better understand its purpose.
I'm not criticizing your standards, but maybe they aren't as perferct as
you'd like to believe.
Please explain with an example why you need to create objects for every
user, when all of the objects are equal in purpose? And what do you mean by
"only used by a single user"? Is this a security issue?
ML|||ML wrote:
> "Name clashes"?
> There are no name clashes if common objects have the default owner
> (dbo).
But there would be if multiple objects with the same name but different
owners existed. I assumed you were referring to this situation when
promoting qualified names.
> If you created these obviously common functions with a
> qualified name (using dbo as the owner), you could prevent any
> problems.
> The pros of using qualified names are simply too numerous to mention
> here.
Impressive. Care to reveal some detail here? All I found so far was
faster access to execution plans and better reuse of execution plans of
stored procedures when called with qualified names.
> In SQL 2005 the owner is replaced by a more powerful quaifier - the
> schema - maybe you should look into it to better understand its
> purpose.
I might if I have the time. But ATM we're on 2k so I wouldn't want to
complicate things by mixing in concepts we can't use anyway. :-)
> I'm not criticizing your standards, but maybe they aren't as perferct
> as you'd like to believe.
I'm far from believing they are perfect. They just exist. And there is
some effort attached to changing them. Hence my conservatism. :-)
> Please explain with an example why you need to create objects for
> every user, when all of the objects are equal in purpose? And what do
> you mean by "only used by a single user"? Is this a security issue?
I don't create the same objects for every user. There is just one (1)
database user (apart from dbo). All tables, views, procedures and
functions are created unqualified and thus belong to this single user.
I'd like to stick with that simple policy. Unfortunately you can create
functions unqualified but you have to invoke them qualified. I don't see
why this is or has to be but apparently it's the way things are. Do you
have an idea why they did it this way?
I'll check whether the privileges our app requires are sufficient to
create the function for user dbo. That seems the second best alternative.
Hm, it seems creating is ok but invoking is prohibited. Darn. Seems like
I have to go down the preprocessing road.
Cheers
robert|||Ok, simply put most benefits of using qualified names are related to
performance. As you've mentioned. Another might be context of use, yet in
your case I see no need for that, since you in fact deal with a single user
(owner). In a multi role environment it could provide more clarity if method
s
that basically serve the same purpose for a variety of roles need to provide
special context-related features, which can be simplified using different
owners while keeping one name. E.g. a procedure to get product details might
return a different result for owner Engineering and a different one for owne
r
Marketing.
If you are using a single owner, why not use dbo?
But on the other hand, your only issue is with invoking functions, so either
way using a qualified name is mandatory. Simply because MS SQL Server says
so. :)
ML|||ML wrote:
> Ok, simply put most benefits of using qualified names are related to
> performance. As you've mentioned. Another might be context of use,
> yet in your case I see no need for that, since you in fact deal with
> a single user (owner). In a multi role environment it could provide
> more clarity if methods that basically serve the same purpose for a
> variety of roles need to provide special context-related features,
> which can be simplified using different owners while keeping one
> name. E.g. a procedure to get product details might return a
> different result for owner Engineering and a different one for owner
> Marketing.
Ok, thanks for clarifying.
> If you are using a single owner, why not use dbo?
I would but it seems the permissions we require for the DB user are
sufficient to create a function with owner dbo but not to invoke it later.
For the time being I'll stick with the preprocessing (i.e. the actual user
name is injected before SQL is executed).
> But on the other hand, your only issue is with invoking functions, so
> either way using a qualified name is mandatory. Simply because MS SQL
> Server says so. :)
Yep. Still I don't have an idea why this is so - I suspect the words
"historic" and "legacy" would be used in an explanation... :-)
Again, thanks for helping out!
Kind regards
robert
Thursday, March 22, 2012
create a function
hi, I try to do insert a stored procedure's result into a temp table,
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>
Saturday, February 25, 2012
Coverting varchar to numeric
I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com
Friday, February 24, 2012
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
>
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
>
Friday, February 17, 2012
counting number of specific rows in SQL
hi,
I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.
Thanks,
Dror.
I don't think a SQL Server function is the best option for you. If you are returning this to SSRS, you'll probably want to leverage a stored procedure. Still, here is a generic representation of what you're asking for:
create function dbo.MyFunction( @.MyParam varchar(10) )
returns @.ret table (MyCount int, MyAvg float)
begin
insert into @.ret (MyCount, MyAvg)
select count(*), avg(MyField) from MyTable where MyOtherField=@.MyParam
return
end
This function returns a table. To retrieve data, you will call it as follows:
select *
from dbo.MyFunction( @.MyParam)
Again, I don't think this is the best approach to this problem. There are more easily read and consumed approaches to this problem.
Thanks,
Bryan
A VB function is not a good choice either. Are you using this data in SSRS?
Thanks,
Bryan
Hi Bryan,
To tell the truth - I do not even know what SSRS is...I am new to this field, still learning. I tried to minimize my application for security (I'm affraid of paople will restore my code and see my logic creating the code), so some of the important stuff will perform as stored procedures on the SQL (which I'm also learning now...) Now I managed to calculate the average, and count the rows. But my function needs to return the result in a variable: the first variable - numbers of rows, the second - the average of a column, and the third - I need to find the fifth biggest value in a specific column. The prolem is assigning the results into variables.
Thanks again for your help,
Dror.
|||Try something like this with appropriate substitutions. Please note, the data types I'm using for the parameters does not imply that these would be the approapriate data types for your application.
Code Snippet
create proc MyProc @.MyParam varchar(10), @.MyCount int OUTPUT, @.MyAvg float OUTPUT
as
select @.MyCount = count(*), @.MyAvg = avg(MyField)
from MyTable
where MyOtherField = @.MyParam
return 0
end
You can look up more info on using output parameters by reading the Books Online entry for the CREATE PROCEDURE statement.
Thanks,
Bryan Smith
Counting No Of Words In a Column
Hi,
for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.
for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this
thanksIs this too simple?
DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))|||BTW - if it isn't then it is vastly more efficient.|||Is this too simple?
DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))
one more qn..
wat if the data contains blank space
e.g. : 'This Is A Sentence '|||Bol
Rtrim(ltrim())|||Bol
Rtrim(ltrim())
Thank U all for the comments...
:)|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."
LEN(REPLACE(@.Text, ' ', ' ')) - LEN(REPLACE(REPLACE(@.Text, ' ', ' '), ' ', '')) +1 :)|||You're gonna ask about triple spaces now ain't cha?|||Yup. I am.
To make this robust, you need a loop to eliminate double spaces until none remain. And that means this should be a multi-step function rather than a simple formula.|||Does the OP live in Iceland?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1|||... you need a loop ...muthaf*$#%$n loops in a muthaf*$#%$n function?
next you're going to suggest using a cursor, aren't you
:)
for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.
for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this
thanksIs this too simple?
DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))|||BTW - if it isn't then it is vastly more efficient.|||Is this too simple?
DECLARE @.Text AS VarChar(100)
SET @.Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@.Text) - LEN(REPLACE(@.Text, ' ', '')) +1 AS VarChar(3))
one more qn..
wat if the data contains blank space
e.g. : 'This Is A Sentence '|||Bol
Rtrim(ltrim())|||Bol
Rtrim(ltrim())
Thank U all for the comments...
:)|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."|||A bigger issue would be if the text contains double spaces:
"This is a single line of text. This text contains two sentences separated by two space characters."
LEN(REPLACE(@.Text, ' ', ' ')) - LEN(REPLACE(REPLACE(@.Text, ' ', ' '), ' ', '')) +1 :)|||You're gonna ask about triple spaces now ain't cha?|||Yup. I am.
To make this robust, you need a loop to eliminate double spaces until none remain. And that means this should be a multi-step function rather than a simple formula.|||Does the OP live in Iceland?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1|||... you need a loop ...muthaf*$#%$n loops in a muthaf*$#%$n function?
next you're going to suggest using a cursor, aren't you
:)
Subscribe to:
Posts (Atom)