Showing posts with label string. Show all posts
Showing posts with label string. Show all posts

Wednesday, March 21, 2012

Fuzzy grouping matching nulls to empty strings/spaces

Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?

(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).

Thanks in advance.

Ben

Not sure, but building a quick package and adding a data viewer should give you the answer.

I would hope that a NULL wouldn't match to spaces...

Couldn't you run an update query to "fix" the data?|||

Computer says "no"

<cough>

No duplicate matching between null and empty string/spaces, as you suspected. Thanks for the idea of the quick test - seems obvious in retrospect, if only it had been beforehand Smile Unfortunately I can't fix the data, other than rerunning the grouping task, but good to know the source of the problem - thanks Phil!

Monday, March 19, 2012

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Monday, March 12, 2012

function to trim leading zeros

Is there any function to remove leading zeros out of a string
for ex i need
001024314B1AXP to be conveted to 1024314B1AXP
Thanks"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CBE08779-D394-431E-8AE6-CFAAAE0A4B5B@.microsoft.com...
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks
>
It's not pretty, but you can take a look at CHARINDEX
Rick Sawtell|||Hmmm. You could probably write one, but it wouldn't be too efficient in
SQL. Probably better to do it either before you store the data or in your
presentation layer. That said, here's a UDF to do it for you:
CREATE FUNCTION dbo.udf_StripLeadZeros (@.s VARCHAR(255))
RETURNS VARCHAR (255) AS
BEGIN
DECLARE @.i INTEGER
SET @.i = 1
DECLARE @.Done CHAR(1)
SET @.Done = 'F'
WHILE (@.i <= LEN(@.s)) AND (@.Done = 'F')
BEGIN
IF (SUBSTRING(@.s, @.i, 1) = '0')
SET @.i = @.i + 1
ELSE
SET @.Done = 'T'
END
RETURN SUBSTRING(@.s, @.i, LEN(@.s) - @.i + 1)
END
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:CBE08779-D394-431E-8AE6-CFAAAE0A4B5B@.microsoft.com...
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks
>|||Try this:
SELECT REPLACE(LTRIM(REPLACE( '001024314B1AXP' ,'0',' ')),' ','0')
Gert-Jan
Sanjay wrote:
> Is there any function to remove leading zeros out of a string
> for ex i need
> 001024314B1AXP to be conveted to 1024314B1AXP
> Thanks|||On Wed, 20 Apr 2005 22:44:58 +0200, Gert-Jan Strik wrote:
>Try this:
>SELECT REPLACE(LTRIM(REPLACE( '001024314B1AXP' ,'0',' ')),' ','0')
Or this:
SELECT SUBSTRING('001024314B1AXP',
PATINDEX('%[^0]%', '001024314B1AXP'),
LEN('001024314B1AXP'))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Function to return substring letters only

I am hoping someone can help me with an example of a user function that will
return just the letters of a string. For example, sending 1RMB23 will return
RMB. Thank you.CREATE FUNCTION ReturnNonNumeric (@.Source varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @.ReturnNonNumeric varchar(100)
SET @.ReturnNonNumeric = REPLACE(@.Source, '0', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '1', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '2', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '3', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '4', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '5', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '6', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '7', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '8', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '9', '')
RETURN (@.ReturnNonNumeric)
END
--kludgy, but will do what you want. Stu|||quickly wrote this (just for fun)
CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
RETURNS varchar(255)
AS
BEGIN
Declare @.nPos Int,@.Strip varchar(150)
Set @.nPos=1
Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
While @.nPos<=Len(@.Strip)
Begin
Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
Set @.nPos=@.nPos+1
End
Return @.String
END|||Wow; that's nice.
<golf clap>
Stu|||Thanks, but if I thunk'd a moment longer, I would have trapped the single
quotes.
Return Replace(@.String,'','')|||Elegant, thank you. And thanks to Stu. I learned from yours also.
"John Cappelletti" wrote:

> quickly wrote this (just for fun)
> CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
> RETURNS varchar(255)
> AS
> BEGIN
> Declare @.nPos Int,@.Strip varchar(150)
> Set @.nPos=1
> Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
> While @.nPos<=Len(@.Strip)
> Begin
> Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
> Set @.nPos=@.nPos+1
> End
> Return @.String
> END
>|||Sorry, I was clear until your "moment longer." You've got all single quotes.
You're trapping a string of 5 single quotes and replacing with two single
quotes? Where does that come from?
"John Cappelletti" wrote:

> Thanks, but if I thunk'd a moment longer, I would have trapped the single
> quotes.
> Return Replace(@.String,'','')
>|||I omitted the single quote from the strip string (and space).
Since I grab only 1 character at a time from the strip string, one final
replace is required, and the only way to put a single quote within single
quotes is to double them up.
The 5 should be 4 (I'm distracted by a bad movie)
Return Replace(@.String,'''','')
Good catch!
John
"richardb" wrote:
> Sorry, I was clear until your "moment longer." You've got all single quote
s.
> You're trapping a string of 5 single quotes and replacing with two single
> quotes? Where does that come from?
> "John Cappelletti" wrote:
>

function to return multi-selected values?

Is there a function to return the multi-selected parm values in a comma
delimited string? Using RS2005. dataset1 is the source of a parameter I'll
call STORE. I have this defined as a multi-select parm. Getting error
(cannot add multi value query parameter 'STORE' for dataset 'dataset2'
because it is not supported by the data extension) on trying to select parms
that are needed for my next dataset query unless I map the parameter for
dataset2 to an expression like this STORE = Parameters!STORE.Value(0) + "," +
Parameters!STORE.Value(1)
I cant do that in reality though - I was just seeing if it would work. I
need a function that I can map the parameter to which will feed to dataset2
all the STORE values chosen (similar to above). Why does it say that it is
not supported when I can type in comma delimited values in that parameter
field and run the report? Thanks in advanceIf I understand you have this...
Parameter 1, STORE, multi-select. Source is from query (dataset1) -
something like SELECT STORE_NAME FROM STORE.
dataset2 feeds the data in your report. It has to have a where clause
based on the values selected from the STORE parameter.
You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
are using a supported database: SQL Server or... i think... Oracle).
If you are using another dbms (we use sybase for some of our reports,
and we get that error) you have to be creative...
In that case, for dataset2, I have done the following.
="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
Join(Parameters!STORE.Value, "','") & "'"
There are single quotes in there to put them around each value in STORE
(assuming it is a string). If they are integers, you can lose the
single quotes.
Good luck,
Regards,
Dan|||Thanks Dan,
Actually I am using db2 version 8.1 (database lives on a unix box). You
understood correctly that I have dataset1 which provides the selections for
the parm STORE. The parm is then mapped to a query parm that is needed for
dataset2 which is a stored procedure (no sql to manipulate there). Any ideas
there? Again ... thanks
"Dan" wrote:
> If I understand you have this...
> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> something like SELECT STORE_NAME FROM STORE.
> dataset2 feeds the data in your report. It has to have a where clause
> based on the values selected from the STORE parameter.
> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> are using a supported database: SQL Server or... i think... Oracle).
> If you are using another dbms (we use sybase for some of our reports,
> and we get that error) you have to be creative...
> In that case, for dataset2, I have done the following.
> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> Join(Parameters!STORE.Value, "','") & "'"
> There are single quotes in there to put them around each value in STORE
> (assuming it is a string). If they are integers, you can lose the
> single quotes.
> Good luck,
> Regards,
> Dan
>|||When you pass a multi-select parameter to a stored procedure you are sending
a comma separated string of values. Why your stored procedure is having
trouble with it I don't know.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> Thanks Dan,
> Actually I am using db2 version 8.1 (database lives on a unix box). You
> understood correctly that I have dataset1 which provides the selections
> for
> the parm STORE. The parm is then mapped to a query parm that is needed
> for
> dataset2 which is a stored procedure (no sql to manipulate there). Any
> ideas
> there? Again ... thanks
> "Dan" wrote:
>> If I understand you have this...
>> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
>> something like SELECT STORE_NAME FROM STORE.
>> dataset2 feeds the data in your report. It has to have a where clause
>> based on the values selected from the STORE parameter.
>> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
>> are using a supported database: SQL Server or... i think... Oracle).
>> If you are using another dbms (we use sybase for some of our reports,
>> and we get that error) you have to be creative...
>> In that case, for dataset2, I have done the following.
>> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
>> Join(Parameters!STORE.Value, "','") & "'"
>> There are single quotes in there to put them around each value in STORE
>> (assuming it is a string). If they are integers, you can lose the
>> single quotes.
>> Good luck,
>> Regards,
>> Dan
>>|||I dont know either. If I take the multi-value off of that parm and type in 2
comma separated values then it works just fine. Something about the
multi-value it doesnt like or I am setting up wrong. This is db2 v8.1 ...
the stored proc works to accept comma-separated values when typed in ... just
not when selected as multi-value ... I cant see what I would be doing wrong?
"Bruce L-C [MVP]" wrote:
> When you pass a multi-select parameter to a stored procedure you are sending
> a comma separated string of values. Why your stored procedure is having
> trouble with it I don't know.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> > Thanks Dan,
> > Actually I am using db2 version 8.1 (database lives on a unix box). You
> > understood correctly that I have dataset1 which provides the selections
> > for
> > the parm STORE. The parm is then mapped to a query parm that is needed
> > for
> > dataset2 which is a stored procedure (no sql to manipulate there). Any
> > ideas
> > there? Again ... thanks
> >
> > "Dan" wrote:
> >
> >> If I understand you have this...
> >>
> >> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> >> something like SELECT STORE_NAME FROM STORE.
> >>
> >> dataset2 feeds the data in your report. It has to have a where clause
> >> based on the values selected from the STORE parameter.
> >>
> >> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> >> are using a supported database: SQL Server or... i think... Oracle).
> >>
> >> If you are using another dbms (we use sybase for some of our reports,
> >> and we get that error) you have to be creative...
> >>
> >> In that case, for dataset2, I have done the following.
> >>
> >> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> >> Join(Parameters!STORE.Value, "','") & "'"
> >>
> >> There are single quotes in there to put them around each value in STORE
> >> (assuming it is a string). If they are integers, you can lose the
> >> single quotes.
> >>
> >> Good luck,
> >>
> >> Regards,
> >>
> >> Dan
> >>
> >>
>
>|||Bruce may be better versed in some of this than I am, but from what I
understand, a comma separated list is only sent through to the dbms
through a data extension that supports multi-value parameters. I don't
believe db2/odbc does.
What does the text from your dataset 2 look like?
If you changed it to something like the following, it should work.
="exec storedprocname '" & Join(Parameters!STORE.Value, "','") & "'"

Function to remove accent in string

Hi,

Does anyone have a function which replaces accent chars from a string
with the non-accented equivalent? For example 'hpital' should return
'hopital'.

Thank you in advance.Is this so that you can compare differently accented strings? If so and if
you are using SQL2000 then there is no need actually to replace the accented
characters. Just use an accent-insensitive collation for your comparisons:

IF 'hpital'='hopital' COLLATE Latin1_General_CI_AI
PRINT 'YES'

This avoids an expensive update. Better still, if you can change the column
collation to be accent-insensitive then you can create an index on the
column to help with the comparison.

Otherwise you could replace the accented chars like this:

UPDATE YourTable SET col =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(col COLLATE Latin1_General_CI_AI
,'a','a'),'b','b'),'c','c'),'d','d'),'e','e'),'f', 'f')
,'g','g'),'h','h'),'i','i'),'j','j'),'k','k'),'l', 'l')
,'m','m'),'n','n'),'o','o'),'p','p'),'q','q'),'r', 'r')
,'s','s'),'t','t'),'u','u'),'v','v'),'w','w'),'x', 'x')
,'y','y'),'z','z')

Again, this assumes you are using 2000. If correct case is important to you
then specify a case-sensitive collation in place of Latin1_General_CI_AI and
add nested REPLACE statements for all the upper-case letters too.

--
David Portas
SQL Server MVP
--|||Thanks David.

I tried changeing the column to be accent-insensitive as you suggest
and indeed it does work when using a simple SQL statement with a WHERE
clause (WHERE 'hpital'='hopital'). However, I am using freetext
search on this column and when I use CONTAINSTABLE there is no result
found for 'hopital' it only works for 'hpital'. Do you have any ideas
how I can get this to work without using a replace function?

Darren.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<pr2dnfJO_6QZCTzcRVn-tw@.giganews.com>...
> Is this so that you can compare differently accented strings? If so and if
> you are using SQL2000 then there is no need actually to replace the accented
> characters. Just use an accent-insensitive collation for your comparisons:
> IF 'hpital'='hopital' COLLATE Latin1_General_CI_AI
> PRINT 'YES'
> This avoids an expensive update. Better still, if you can change the column
> collation to be accent-insensitive then you can create an index on the
> column to help with the comparison.
> Otherwise you could replace the accented chars like this:
> UPDATE YourTable SET col =
> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(col COLLATE Latin1_General_CI_AI
> ,'a','a'),'b','b'),'c','c'),'d','d'),'e','e'),'f', 'f')
> ,'g','g'),'h','h'),'i','i'),'j','j'),'k','k'),'l', 'l')
> ,'m','m'),'n','n'),'o','o'),'p','p'),'q','q'),'r', 'r')
> ,'s','s'),'t','t'),'u','u'),'v','v'),'w','w'),'x', 'x')
> ,'y','y'),'z','z')
> Again, this assumes you are using 2000. If correct case is important to you
> then specify a case-sensitive collation in place of Latin1_General_CI_AI and
> add nested REPLACE statements for all the upper-case letters too.|||I'm not an expert with Full Text but see this thread:
http://www.google.com/groups?hl=en&...FTNGP12.phx.gbl

--
David Portas
SQL Server MVP
--

Function to find character in string

Hi:
I was looking through BOL for a function that would return the
numerical place where a string first occurs (similar to Crystal
Report's 'instr' function):
so
FUNKYFUNCTION('abcdefgd','d',1)
would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
argument is the starting place in the string.
Basically, what I want to do is trim everything after the first space
is found. I might have certain field values that end up as:
5445 UNWANTEDSTRINGETXT
I want this field to be changed to '5445' when it finds unwanted text.
So having this function would make this easy. Or maybe there is another
way in the absence of such a function?
Thanks for the help,
Kaydadeclare @.str varchar(80)
set @.str = '5445 UNWANTEDSTRINGETXT'
SELECT CHARINDEX(' ', @.str),
CASE WHEN CHARINDEX(' ', @.str) = 0
THEN @.str
ELSE SUBSTRING(@.str,1,CHARINDEX(' ', @.str)-1)
END
On 16 Feb 2006 16:13:03 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>I was looking through BOL for a function that would return the
>numerical place where a string first occurs (similar to Crystal
>Report's 'instr' function):
>so
>FUNKYFUNCTION('abcdefgd','d',1)
>would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
>argument is the starting place in the string.
>Basically, what I want to do is trim everything after the first space
>is found. I might have certain field values that end up as:
>5445 UNWANTEDSTRINGETXT
>I want this field to be changed to '5445' when it finds unwanted text.
>So having this function would make this easy. Or maybe there is another
>way in the absence of such a function?
>Thanks for the help,
>Kayda|||The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.
Regards,
Willson
"Kayda" wrote:

> Hi:
> I was looking through BOL for a function that would return the
> numerical place where a string first occurs (similar to Crystal
> Report's 'instr' function):
> so
> FUNKYFUNCTION('abcdefgd','d',1)
> would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
> argument is the starting place in the string.
> Basically, what I want to do is trim everything after the first space
> is found. I might have certain field values that end up as:
> 5445 UNWANTEDSTRINGETXT
> I want this field to be changed to '5445' when it finds unwanted text.
> So having this function would make this easy. Or maybe there is another
> way in the absence of such a function?
> Thanks for the help,
> Kayda
>

Function similar to ISNULL()

I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.You could write your own User Defined Function. Check out Books On Line for more info.
|||


Try the links below you may find T-SQL funtion to do what you need, but remember ISNULL can give just plain wrong numbers with OUTER JOIN in SQL Server, it is a known issue. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

http://www.samspublishing.com/articles/article.asp?p=31283&seqNum=3&rl=1

Friday, March 9, 2012

Function results

I am trying to convert a string value, from a parameter field, into a date t
o
insert into a SQL2005 db smalldatetime field. The function returns a bit
value, which I test by using the isdate function, and if true convert this t
o
a smalldatetime value, else I set to null.
I get error that "Syntax incorrect at @.test".
What is incorrect about what I am trying to do'
...
DECLARE @.RecNumber bigint,
@.AGM1 smalldatetime,
@.AGM2 smalldatetime,
@.SDt smalldatetime,
@.Test bit
SET NOCOUNT ON;
@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
if @.test = 1 then
@.AGM1 = convert(varchar(10),@.Adv_Met1,101)
else
@.AGM1 = null
end if
Any help is appreciated.@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
Yes, there is! This is not VBScript, you need to use SET or SELECT
SET @.test = dbo.fn_testdate(@.Adv_Met1);
SELECT @.test = dbo.fn_testdate(@.Adv_Met1);
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message
news:ABD935E6-40A8-4F73-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
>to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
> to
> a smalldatetime value, else I set to null.
> I get error that "Syntax incorrect at @.test".
> What is incorrect about what I am trying to do'
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
> Any help is appreciated.|||> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
Also a bunch of problems here. I'm going to assume you come from a VB or
VBScript background?
IF @.Test = 1 -- there is no THEN with IF in T-SQL
SET @.AGM1 = ... -- again, need SET or SELECT
ELSE -- this line was okay!
SET @.AGM1 = NULL -- you don't really need to do this though, it is
already NULL!
-- END IF -- there is no such thing as END IF in T-SQL
A more structured way, and my prefered way, if you really needed the ELSE
clause, would be:
IF @.Test = 1
BEGIN
SET @.AGM1 = ...
END
ELSE
BEGIN
SET @.AGM1 = NULL
END
While it makes the code more verbose, I see two benefits:
(1) you really know where the boundaries of the conditional are, unless your
indenting / coding conventions are so bad that a BEGIN/END struct don't
help.
(2) if you need to add more statements to the result of a conditional,
you're already set. I find the lazier syntax leads to unexpected behavior,
not sure if people coming from lisp or cobol expect indentation and white
space to mean more than the code itself, but they can't seem to understand
why the following always prints 'bar' no matter what the value of @.foo:
DECLARE @.foo TINYINT;
SET @.foo = 2;
IF @.foo = 1
PRINT 'foo';
PRINT 'bar'; -- they expect this to run
-- with BEGIN / END it would be much more intuitive
IF @.foo = 2
PRINT 'blat';|||>> I am trying to convert a string value, from a parameter field, into a date to insert int
o a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I te
st by using the isdate function, and if true [sic] convert this to a smal
ldatetime value, else I set to null. <<
Your whole approach is wrong. Columns are not fields -- nothing alike.
Formatting of data is done in the front end and not in the database.
Good SQL do not write with BIT data since it is too low-level, avoid
BIGINT since it is absurdly large and avoid the proprietary
SMALLDATETIME type. SQL has no BOOLEAN data types. The ISO-11179
Standard prohibits that silly "fn-" affix on name. The use of numbers
in a data element name are a sign of repeating groups and 1NF
violations.
And you are full of syntax and conceptual errors in your effort to
mimic a non-SQL procedural language. Why did you use CONVERT()? To
get a string!! Temporal datatypes do not exist in the 3GL language you
are trying to mimci and you do not understand them. That is too
abstract, so you nee to see a picture.
Why did you use IF-THEN_ELSE constructs? Because you do not understand
CASE expressions. You do not understand declarative programming, so
you avoid it with the proprietary 20+ year old T-SQL 4GL instead.
If you must do this kind of kludging, try something like this:
SET @.agm = CAST (@.adv_met1 AS DATETIME);
Let the CAST() catch the errors then handle them. I also hope you know
about ISO-8601 formats for temporal data.|||It seems like your functions 'should' be doing the test and the conversion a
nd handing you back the date value you desire. Something like this:
CREATE FUNCTION dbo.fn_testdate
( @.StringDateToConvert as varchar(25) )
RETURNS datetime
AS
IF isdate( @.StringDateToConvert )
RETURN convert( datetime, @.StringDateToConvert, 101 )
ELSE
RETURN NULL
GO
This eliminates the entire 'If @.Test' block.
Check in Books on Line for the proper 'Style' value (the 101 above.) Look up
CAST and CONVERT and note the 'Style' indicators.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message news:ABD935E6-40A8-4F7
3-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
to
> a smalldatetime value, else I set to null.
>
> I get error that "Syntax incorrect at @.test".
>
> What is incorrect about what I am trying to do'
>
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
>
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
>
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
>
> Any help is appreciated.|||>> A more structured way, and my prefered way, if you really needed the ELSE
clause, would be: <<
A more **declarative** way and my prefered way, if you really needed
the ELSE
clause, would be:
SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
Why encourage newbies to mimic a 3GL in SQL when you do not have to?|||Thank you
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also a bunch of problems here. I'm going to assume you come from a VB or
> VBScript background?
> IF @.Test = 1 -- there is no THEN with IF in T-SQL
> SET @.AGM1 = ... -- again, need SET or SELECT
> ELSE -- this line was okay!
> SET @.AGM1 = NULL -- you don't really need to do this though, it is
> already NULL!
> -- END IF -- there is no such thing as END IF in T-SQL
> A more structured way, and my prefered way, if you really needed the ELSE
> clause, would be:
> IF @.Test = 1
> BEGIN
> SET @.AGM1 = ...
> END
> ELSE
> BEGIN
> SET @.AGM1 = NULL
> END
> While it makes the code more verbose, I see two benefits:
> (1) you really know where the boundaries of the conditional are, unless yo
ur
> indenting / coding conventions are so bad that a BEGIN/END struct don't
> help.
> (2) if you need to add more statements to the result of a conditional,
> you're already set. I find the lazier syntax leads to unexpected behavior
,
> not sure if people coming from lisp or cobol expect indentation and white
> space to mean more than the code itself, but they can't seem to understand
> why the following always prints 'bar' no matter what the value of @.foo:
> DECLARE @.foo TINYINT;
> SET @.foo = 2;
> IF @.foo = 1
> PRINT 'foo';
> PRINT 'bar'; -- they expect this to run
> -- with BEGIN / END it would be much more intuitive
> IF @.foo = 2
> PRINT 'blat';
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151678883.305430.63220@.p79g2000cwp.googlegroups.com...
> clause, would be: <<
> A more **declarative** way and my prefered way, if you really needed
> the ELSE
> clause, would be:
> SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
> Why encourage newbies to mimic a 3GL in SQL when you do not have to?
Because unlike you, I don't expect them to become SQL gurus overnight. :-)
Let's let them get the syntax figured out, then the optimal way to do
things. Much of this they'll find out on their own, because as you and I
both know, the "experts" don't always agree on the best way to do something.
A

Function Keys in Query Manager

This question is surely a general PC question, but someone
here surely knows how to program a hotkey, or funciton key
F6, F7, etc. to put in a string of text to avoid some
typing. I'd like to hit F12 and automatically get:
SELECT * FROM objects WHERE objectid =
I'm having to type this in ALL OF THE TIME.
Any help appreciated.
thanks,
--Bob
Open the Object Browser and click on the Objects tab at the bottom. Drill down to your favourite table, right click and select one of the scripting options.
You're also able to add your own templates as well as using the defaults.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"BobS" <anonymous@.discussions.microsoft.com> wrote in message news:1aeaa01c41e8d$1bf687e0$a101280a@.phx.gbl...
This question is surely a general PC question, but someone
here surely knows how to program a hotkey, or funciton key
F6, F7, etc. to put in a string of text to avoid some
typing. I'd like to hit F12 and automatically get:
SELECT * FROM objects WHERE objectid =
I'm having to type this in ALL OF THE TIME.
Any help appreciated.
thanks,
--Bob

Wednesday, March 7, 2012

function -> first letter of each word - capital

Hello
I'm looking for a ready to use user function which takes a string as a
argument and returns string containing fist letter of each word as capital.
example:
initail: sUN iS ShINIng
result: Sun Is Shining
The similar role in Oracle has function called INITCAP
Best Regards
Darek T.http://www.devx.com/tips/Tip/17608
Aneesh
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>|||If you are using SQL Server 2005 then use the FOR XML enhancements, see my
blog entry:
http://sqlblogcasts.com/blogs/tonyr.../06/20/832.aspx
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>

function -> first letter of each word - capital

Hello
I'm looking for a ready to use user function which takes a string as a
argument and returns string containing fist letter of each word as capital.
example:
initail: sUN iS ShINIng
result: Sun Is Shining
The similar role in Oracle has function called INITCAP
Best Regards
Darek T.http://www.devx.com/tips/Tip/17608
Aneesh
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>|||If you are using SQL Server 2005 then use the FOR XML enhancements, see my
blog entry:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/20/832.aspx
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>

Friday, February 24, 2012

full-text search of SP/trigger source?

Is there any Microsoft utility that will search through the source of SPs,
triggers, and functions, looking for a string? (Something beyond the
dependencies listing).
Thanks
TimoThere isn't anything built-in that I'm aware of. You can either script the
objects to files using Enterprise Manager and then search the files or run a
query like the one below to search syscomments. Note that the query method
isn't 100% accurate since a string may be split between 2 rows.
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%MyString%'
Hope this helps.
Dan Guzman
SQL Server MVP
"Timo" <timo@.noneofyer.biz> wrote in message
news:usSnZrvEFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is there any Microsoft utility that will search through the source of SPs,
> triggers, and functions, looking for a string? (Something beyond the
> dependencies listing).
> Thanks
> Timo
>|||Timo
Vyas has written a great script
DROP PROCEDURE sp_FindObject
GO
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc WHERE (so.xtype
='TR' or .....) AND so.id = sc.id
OPEN Obj
FETCH Next FROM Obj INTO @.Name,@.Text
WHILE @.@.FETCH_STATUS=0
BEGIN
IF PATINDEX(@.SearchString,@.Text) <> 0
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name,@.Text
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
go
EXEC sp_FindObject '%HOST_ID()%'
"Timo" <timo@.noneofyer.biz> wrote in message
news:usSnZrvEFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is there any Microsoft utility that will search through the source of SPs,
> triggers, and functions, looking for a string? (Something beyond the
> dependencies listing).
> Thanks
> Timo
>|||Dan, Uri
Thank you both for the help!
Timo