Need help comparing output

Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0  =; = Update InvPrice set SellingPrice =3D 222 &nbs=p;  =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.

---

--=_NextPart_000_0028_01C3C8D7.32408980--

need expression help/guidance

Do all expressions have to be on one line?
Can you declare variables in them like in Crystal Reports Formulas?
Can you use other formulas as values? As in you set up a textbox that
will give you a value with an expression, then in another expression
you reference the value of that text box?
Is there a decent guide out there for expressions? Why
can'texpressions just behave similar to a function?
The answers to this post will certainly help determine whether or not
we stick with Crystal or go with this.You can use GlobalVariables in RS :
In Report Properties, go in Custom Code and Declare typed Variables
Exemple : Public Test As String = "100"
You can reference this variables in any expression in your report :
=Code.Test
In any expression formula you can call Custom Code variables or functions.
As well as reference other Report Items:
=ReportItems!textbox4.Value (to use a textbox named textbox4 value).
Let me know if this helps.
Regards,
Davy Ramirez
Winsight - Paris.
"RickB" <rbinns@.nationaldiabetic.com> wrote in message
news:7f3528da.0410150525.60ae7975@.posting.google.com...
> Do all expressions have to be on one line?
> Can you declare variables in them like in Crystal Reports Formulas?
> Can you use other formulas as values? As in you set up a textbox that
> will give you a value with an expression, then in another expression
> you reference the value of that text box?
> Is there a decent guide out there for expressions? Why
> can'texpressions just behave similar to a function?
> The answers to this post will certainly help determine whether or not
> we stick with Crystal or go with this.|||You may want to read this MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RickB" <rbinns@.nationaldiabetic.com> wrote in message
news:7f3528da.0410150525.60ae7975@.posting.google.com...
> Do all expressions have to be on one line?
> Can you declare variables in them like in Crystal Reports Formulas?
> Can you use other formulas as values? As in you set up a textbox that
> will give you a value with an expression, then in another expression
> you reference the value of that text box?
> Is there a decent guide out there for expressions? Why
> can'texpressions just behave similar to a function?
> The answers to this post will certainly help determine whether or not
> we stick with Crystal or go with this.

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.

Need a way to force MS SQL to NOT ignore trailing spaces in variables.

Here's what I've got. I have need of a function to parse out a piece
of data from a text string that falls after a specific piece of text.
Ex: Large String Value: 'my big string of stuff'
Key piece of data value trails: 'big '
# of chars to pull: 2
So I've created a simple function (see below)
The issue is that using this function in the syntax below yields the
wrong data due to spaces being truncated in some situations but not
others. So in the below test of the function I've created you can see
that the 2 characters immediately following 'big ' are 'st'. However
since I can't find an accurate way to get the length of 'big
' (returns 3 instead of 4) I always end up with ' s' as the result.
IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
BEGIN
PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
END
Also take this example. The trailing spaces are preserved as part of
the value but not properly processed by the length method.
DECLARE @.a varchar(5)
DECLARE @.b varchar(5)
SET @.a = 'a '
SET @.b = 'b '
SELECT @.a, LEN(@.a), @.b, LEN(@.b), @.a + @.b, LEN(@.a + @.b)
-- -- -- -- -- --
a 1 b 1 a b 4
And my last argument (although pointless) this SQL will continue until
int is maxed out since apparently it has no way of knowing when it is
at the end of a varchar value. I would expect it to fail accessing
index's past the length of the varchar value but it just keeps going
returning empty strings.
DECLARE @.a varchar(4)
SET @.a = 'a '
DECLARE @.idx int
SET @.idx = 1
loop:
BEGIN
SELECT SUBSTRING(@.a, @.idx, 1)
SELECT @.idx = @.idx + 1
GOTO loop
END
So all that said I need a way to get my function to properly evaluate
a string with a trailing space. I'm out of ideas as it's quite
obvious MS SQL thinks it knows best. And ANSI_PADDING makes no
difference on variables I've verified .
Also
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
----
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.StringPart'))
DROP FUNCTION dbo.StringPart
GO
/
******************************************************************************
* StringPart will return the length of string from inside of another
based
* off a preceding string value. It will only return the first
satisfying part
* from the source string.
* @.initial as nvarchar(4000) - the string value to pull a piece from
* @.search_str as nvarchar(4000) - the preceeding string value to the
part
* desired.
* @.part_len as int - the part of the string you wish to pull out that
follows
* the search string value (@.search_str)in the larger @.initial
string.
*
* return nvarchar(4000) - the @.part_len number of characters
following after
* the first occurance of @.search_str in the @.initial string. If
the
* number of characters would have exceeded the length of the
original
* string then only the available length of characters will be
returned.
*****************************************************************************/
----
CREATE FUNCTION dbo.StringPart (
@.initial as nvarchar(4000),
@.search_str as nvarchar(4000),
@.part_len as int
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Can't use '' as ' ' will end up evaluating to true which is stupid
but
-- none the less an alternative was needed. If one of our sources
is equal
-- to this particular sting then i'd guess our source has issues.
IF (ISNULL(@.search_str, '!!##~~~~===~~~~===') = '!!##~~~~===~~~~===')
RETURN NULL
-- We can't return a string larger than 4000 char's so if the len is
greater
-- then that we can return NULL as an error response.
IF (@.part_len > 4000)
RETURN NULL
DECLARE @.str_len int
DECLARE @.idx int
DECLARE @.pre_len int
SELECT @.str_len = LEN(@.initial)
SET @.idx = 0
SELECT @.pre_len = LEN(@.search_str)
SELECT @.idx = charindex(@.search_str, @.initial)
IF (@.idx = 0)
BEGIN
RETURN NULL
END
-- Declare the return variable since we are done with all other
returns
DECLARE @.return nvarchar(4000)
-- Now set the idx to the start of the part we want to return instead
of
-- the sart of the preceeding string value.
SELECT @.idx = @.idx + @.pre_len
IF (@.str_len < (@.idx + @.part_len))
BEGIN
-- We extend beyond the end of the string so get all we can
SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx)
END
ELSE
BEGIN
-- pull just the part requested
SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
END
RETURN @.return
END
GOTry looking into the DATALENGTH function instead of LEN
select len('abc '), datalength('abc ')
3 4
<marc.l.caron@.gmail.com> wrote in message
news:1190143353.158985.202430@.y42g2000hsy.googlegroups.com...
> Here's what I've got. I have need of a function to parse out a piece
> of data from a text string that falls after a specific piece of text.
> Ex: Large String Value: 'my big string of stuff'
> Key piece of data value trails: 'big '
> # of chars to pull: 2
> So I've created a simple function (see below)
> The issue is that using this function in the syntax below yields the
> wrong data due to spaces being truncated in some situations but not
> others. So in the below test of the function I've created you can see
> that the 2 characters immediately following 'big ' are 'st'. However
> since I can't find an accurate way to get the length of 'big
> ' (returns 3 instead of 4) I always end up with ' s' as the result.
> IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
> BEGIN
> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
> 2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
> END
> Also take this example. The trailing spaces are preserved as part of
> the value but not properly processed by the length method.
> DECLARE @.a varchar(5)
> DECLARE @.b varchar(5)
> SET @.a = 'a '
> SET @.b = 'b '
> SELECT @.a, LEN(@.a), @.b, LEN(@.b), @.a + @.b, LEN(@.a + @.b)
> -- -- -- -- -- --
> a 1 b 1 a b 4
> And my last argument (although pointless) this SQL will continue until
> int is maxed out since apparently it has no way of knowing when it is
> at the end of a varchar value. I would expect it to fail accessing
> index's past the length of the varchar value but it just keeps going
> returning empty strings.
> DECLARE @.a varchar(4)
> SET @.a = 'a '
> DECLARE @.idx int
> SET @.idx = 1
> loop:
> BEGIN
> SELECT SUBSTRING(@.a, @.idx, 1)
> SELECT @.idx = @.idx + 1
> GOTO loop
> END
>
> So all that said I need a way to get my function to properly evaluate
> a string with a trailing space. I'm out of ideas as it's quite
> obvious MS SQL thinks it knows best. And ANSI_PADDING makes no
> difference on variables I've verified .
> Also
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
>
> ----
> IF EXISTS (SELECT * FROM sysobjects WHERE id => OBJECT_ID(N'dbo.StringPart'))
> DROP FUNCTION dbo.StringPart
> GO
> /
> ******************************************************************************
> * StringPart will return the length of string from inside of another
> based
> * off a preceding string value. It will only return the first
> satisfying part
> * from the source string.
> * @.initial as nvarchar(4000) - the string value to pull a piece from
> * @.search_str as nvarchar(4000) - the preceeding string value to the
> part
> * desired.
> * @.part_len as int - the part of the string you wish to pull out that
> follows
> * the search string value (@.search_str)in the larger @.initial
> string.
> *
> * return nvarchar(4000) - the @.part_len number of characters
> following after
> * the first occurance of @.search_str in the @.initial string. If
> the
> * number of characters would have exceeded the length of the
> original
> * string then only the available length of characters will be
> returned.
> *****************************************************************************/
> ----
> CREATE FUNCTION dbo.StringPart (
> @.initial as nvarchar(4000),
> @.search_str as nvarchar(4000),
> @.part_len as int
> )
> RETURNS nvarchar(4000)
> AS
> BEGIN
> -- Can't use '' as ' ' will end up evaluating to true which is stupid
> but
> -- none the less an alternative was needed. If one of our sources
> is equal
> -- to this particular sting then i'd guess our source has issues.
> IF (ISNULL(@.search_str, '!!##~~~~===~~~~===') = '!!##~~~~===~~~~===')
> RETURN NULL
> -- We can't return a string larger than 4000 char's so if the len is
> greater
> -- then that we can return NULL as an error response.
> IF (@.part_len > 4000)
> RETURN NULL
> DECLARE @.str_len int
> DECLARE @.idx int
> DECLARE @.pre_len int
> SELECT @.str_len = LEN(@.initial)
> SET @.idx = 0
> SELECT @.pre_len = LEN(@.search_str)
> SELECT @.idx = charindex(@.search_str, @.initial)
> IF (@.idx = 0)
> BEGIN
> RETURN NULL
> END
> -- Declare the return variable since we are done with all other
> returns
> DECLARE @.return nvarchar(4000)
> -- Now set the idx to the start of the part we want to return instead
> of
> -- the sart of the preceeding string value.
> SELECT @.idx = @.idx + @.pre_len
> IF (@.str_len < (@.idx + @.part_len))
> BEGIN
> -- We extend beyond the end of the string so get all we can
> SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx)
> END
> ELSE
> BEGIN
> -- pull just the part requested
> SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
> END
> RETURN @.return
> END
> GO
>|||Excelent! Thank you. Here is the new version of the function with
the change and other things fixed/cleaned up. DATALENGTH is what I
needed.
----
IF EXISTS (SELECT * FROM sysobjects WHERE id =OBJECT_ID(N'dbo.StringPart'))
DROP FUNCTION dbo.StringPart
GO
/
******************************************************************************
* StringPart will return the length of string from inside of another
based
* off a preceding string value. It will only return the first
satisfying part
* from the source string.
* @.initial as nvarchar(4000) - the string value to pull a piece from
* @.search_str as nvarchar(4000) - the preceeding string value to the
part
* desired.
* @.part_len as int - the part of the string you wish to pull out that
follows
* the search string value (@.search_str)in the larger @.initial
string.
*
* return nvarchar(4000) - the @.part_len number of characters
following after
* the first occurance of @.search_str in the @.initial string. If
the
* number of characters would have exceeded the length of the
original
* string then only the available length of characters will be
returned.
*****************************************************************************/
----
CREATE FUNCTION dbo.StringPart (
@.initial as nvarchar(4000),
@.search_str as nvarchar(4000),
@.part_len as int
)
RETURNS varchar(4000)
AS
BEGIN
-- Check for NULL or length of 0
IF (@.search_str IS NULL OR DATALENGTH(@.search_str) = 0)
RETURN NULL
-- We can't return a string larger than 4000 char's so if the len is
greater
-- then that we can return NULL as an error response.
IF (@.part_len > 4000)
RETURN NULL
DECLARE @.idx int -- holds start index's as needed
DECLARE @.str_len int -- length of original string
DECLARE @.pre_len int -- length of preceeding string
SET @.idx = 0
-- variables are UNICODE so datalength is 2 times longer then string
characters
SELECT @.str_len = DATALENGTH(@.initial) / 2
SELECT @.pre_len = DATALENGTH(@.search_str) / 2
-- Find the preceeding text string
SELECT @.idx = CHARINDEX(@.search_str, @.initial)
IF (@.idx = 0)
BEGIN
RETURN NULL
END
-- Declare the return variable since we are done with all other
returns
DECLARE @.return nvarchar(4000)
-- Now set the idx to the start of the part we want to return instead
of
-- the sart of the preceeding string value.
SELECT @.idx = @.idx + @.pre_len
IF (@.str_len < (@.idx + @.part_len))
BEGIN
-- We extend beyond the end of the string so get all we can
SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx + 1)
END
ELSE
BEGIN
-- pull just the part requested
SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
END
RETURN @.return
END
GO
IF ('string of st' != dbo.StringPart('my big string of stuff', 'big ',
12))
BEGIN
PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
12) = ' PRINT dbo.StringPart('my big string of stuff', 'big ', 12)
END
IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
BEGIN
PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
END
IF (' stuff' != dbo.StringPart('my big string of stuff', 'of', 12))
BEGIN
PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''of'',
12)' PRINT dbo.StringPart('my big string of stuff', 'of', 12)
END
IF (' big string ' != dbo.StringPart('my big string of stuff', 'my',
12))
BEGIN
PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''my'',
12)' PRINT dbo.StringPart('my big string of stuff', 'my', 12)
END|||Another possibility would be to cast them to a varbinary.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:%23vrS4xi%23HHA.980@.TK2MSFTNGP06.phx.gbl...
> Try looking into the DATALENGTH function instead of LEN
> select len('abc '), datalength('abc ')
> 3 4
>
>
> <marc.l.caron@.gmail.com> wrote in message
> news:1190143353.158985.202430@.y42g2000hsy.googlegroups.com...
>> Here's what I've got. I have need of a function to parse out a piece
>> of data from a text string that falls after a specific piece of text.
>> Ex: Large String Value: 'my big string of stuff'
>> Key piece of data value trails: 'big '
>> # of chars to pull: 2
>> So I've created a simple function (see below)
>> The issue is that using this function in the syntax below yields the
>> wrong data due to spaces being truncated in some situations but not
>> others. So in the below test of the function I've created you can see
>> that the 2 characters immediately following 'big ' are 'st'. However
>> since I can't find an accurate way to get the length of 'big
>> ' (returns 3 instead of 4) I always end up with ' s' as the result.
>> IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
>> BEGIN
>> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
>> 2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
>> END
>> Also take this example. The trailing spaces are preserved as part of
>> the value but not properly processed by the length method.
>> DECLARE @.a varchar(5)
>> DECLARE @.b varchar(5)
>> SET @.a = 'a '
>> SET @.b = 'b '
>> SELECT @.a, LEN(@.a), @.b, LEN(@.b), @.a + @.b, LEN(@.a + @.b)
>> -- -- -- -- -- --
>> a 1 b 1 a b 4
>> And my last argument (although pointless) this SQL will continue until
>> int is maxed out since apparently it has no way of knowing when it is
>> at the end of a varchar value. I would expect it to fail accessing
>> index's past the length of the varchar value but it just keeps going
>> returning empty strings.
>> DECLARE @.a varchar(4)
>> SET @.a = 'a '
>> DECLARE @.idx int
>> SET @.idx = 1
>> loop:
>> BEGIN
>> SELECT SUBSTRING(@.a, @.idx, 1)
>> SELECT @.idx = @.idx + 1
>> GOTO loop
>> END
>>
>> So all that said I need a way to get my function to properly evaluate
>> a string with a trailing space. I'm out of ideas as it's quite
>> obvious MS SQL thinks it knows best. And ANSI_PADDING makes no
>> difference on variables I've verified .
>> Also
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>>
>>
>> ----
>> IF EXISTS (SELECT * FROM sysobjects WHERE id =>> OBJECT_ID(N'dbo.StringPart'))
>> DROP FUNCTION dbo.StringPart
>> GO
>> /
>> ******************************************************************************
>> * StringPart will return the length of string from inside of another
>> based
>> * off a preceding string value. It will only return the first
>> satisfying part
>> * from the source string.
>> * @.initial as nvarchar(4000) - the string value to pull a piece from
>> * @.search_str as nvarchar(4000) - the preceeding string value to the
>> part
>> * desired.
>> * @.part_len as int - the part of the string you wish to pull out that
>> follows
>> * the search string value (@.search_str)in the larger @.initial
>> string.
>> *
>> * return nvarchar(4000) - the @.part_len number of characters
>> following after
>> * the first occurance of @.search_str in the @.initial string. If
>> the
>> * number of characters would have exceeded the length of the
>> original
>> * string then only the available length of characters will be
>> returned.
>> *****************************************************************************/
>> ----
>> CREATE FUNCTION dbo.StringPart (
>> @.initial as nvarchar(4000),
>> @.search_str as nvarchar(4000),
>> @.part_len as int
>> )
>> RETURNS nvarchar(4000)
>> AS
>> BEGIN
>> -- Can't use '' as ' ' will end up evaluating to true which is stupid
>> but
>> -- none the less an alternative was needed. If one of our sources
>> is equal
>> -- to this particular sting then i'd guess our source has issues.
>> IF (ISNULL(@.search_str, '!!##~~~~===~~~~===') = '!!##~~~~===~~~~===')
>> RETURN NULL
>> -- We can't return a string larger than 4000 char's so if the len is
>> greater
>> -- then that we can return NULL as an error response.
>> IF (@.part_len > 4000)
>> RETURN NULL
>> DECLARE @.str_len int
>> DECLARE @.idx int
>> DECLARE @.pre_len int
>> SELECT @.str_len = LEN(@.initial)
>> SET @.idx = 0
>> SELECT @.pre_len = LEN(@.search_str)
>> SELECT @.idx = charindex(@.search_str, @.initial)
>> IF (@.idx = 0)
>> BEGIN
>> RETURN NULL
>> END
>> -- Declare the return variable since we are done with all other
>> returns
>> DECLARE @.return nvarchar(4000)
>> -- Now set the idx to the start of the part we want to return instead
>> of
>> -- the sart of the preceeding string value.
>> SELECT @.idx = @.idx + @.pre_len
>> IF (@.str_len < (@.idx + @.part_len))
>> BEGIN
>> -- We extend beyond the end of the string so get all we can
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx)
>> END
>> ELSE
>> BEGIN
>> -- pull just the part requested
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
>> END
>> RETURN @.return
>> END
>> GO
>|||That doesn't work for me...
declare @.mystring varchar(30)
set @.mystring = 'abc '
select len(cast(@.mystring as varbinary(30)))
3
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:enPvFNj%23HHA.4612@.TK2MSFTNGP03.phx.gbl...
> Another possibility would be to cast them to a varbinary.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
> news:%23vrS4xi%23HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Try looking into the DATALENGTH function instead of LEN
>> select len('abc '), datalength('abc ')
>> 3 4
>>
>>
>> <marc.l.caron@.gmail.com> wrote in message
>> news:1190143353.158985.202430@.y42g2000hsy.googlegroups.com...
>> Here's what I've got. I have need of a function to parse out a piece
>> of data from a text string that falls after a specific piece of text.
>> Ex: Large String Value: 'my big string of stuff'
>> Key piece of data value trails: 'big '
>> # of chars to pull: 2
>> So I've created a simple function (see below)
>> The issue is that using this function in the syntax below yields the
>> wrong data due to spaces being truncated in some situations but not
>> others. So in the below test of the function I've created you can see
>> that the 2 characters immediately following 'big ' are 'st'. However
>> since I can't find an accurate way to get the length of 'big
>> ' (returns 3 instead of 4) I always end up with ' s' as the result.
>> IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
>> BEGIN
>> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
>> 2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
>> END
>> Also take this example. The trailing spaces are preserved as part of
>> the value but not properly processed by the length method.
>> DECLARE @.a varchar(5)
>> DECLARE @.b varchar(5)
>> SET @.a = 'a '
>> SET @.b = 'b '
>> SELECT @.a, LEN(@.a), @.b, LEN(@.b), @.a + @.b, LEN(@.a + @.b)
>> -- -- -- -- -- --
>> a 1 b 1 a b 4
>> And my last argument (although pointless) this SQL will continue until
>> int is maxed out since apparently it has no way of knowing when it is
>> at the end of a varchar value. I would expect it to fail accessing
>> index's past the length of the varchar value but it just keeps going
>> returning empty strings.
>> DECLARE @.a varchar(4)
>> SET @.a = 'a '
>> DECLARE @.idx int
>> SET @.idx = 1
>> loop:
>> BEGIN
>> SELECT SUBSTRING(@.a, @.idx, 1)
>> SELECT @.idx = @.idx + 1
>> GOTO loop
>> END
>>
>> So all that said I need a way to get my function to properly evaluate
>> a string with a trailing space. I'm out of ideas as it's quite
>> obvious MS SQL thinks it knows best. And ANSI_PADDING makes no
>> difference on variables I've verified .
>> Also
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>>
>>
>> ----
>> IF EXISTS (SELECT * FROM sysobjects WHERE id =>> OBJECT_ID(N'dbo.StringPart'))
>> DROP FUNCTION dbo.StringPart
>> GO
>> /
>> ******************************************************************************
>> * StringPart will return the length of string from inside of another
>> based
>> * off a preceding string value. It will only return the first
>> satisfying part
>> * from the source string.
>> * @.initial as nvarchar(4000) - the string value to pull a piece from
>> * @.search_str as nvarchar(4000) - the preceeding string value to the
>> part
>> * desired.
>> * @.part_len as int - the part of the string you wish to pull out that
>> follows
>> * the search string value (@.search_str)in the larger @.initial
>> string.
>> *
>> * return nvarchar(4000) - the @.part_len number of characters
>> following after
>> * the first occurance of @.search_str in the @.initial string. If
>> the
>> * number of characters would have exceeded the length of the
>> original
>> * string then only the available length of characters will be
>> returned.
>> *****************************************************************************/
>> ----
>> CREATE FUNCTION dbo.StringPart (
>> @.initial as nvarchar(4000),
>> @.search_str as nvarchar(4000),
>> @.part_len as int
>> )
>> RETURNS nvarchar(4000)
>> AS
>> BEGIN
>> -- Can't use '' as ' ' will end up evaluating to true which is stupid
>> but
>> -- none the less an alternative was needed. If one of our sources
>> is equal
>> -- to this particular sting then i'd guess our source has issues.
>> IF (ISNULL(@.search_str, '!!##~~~~===~~~~===') = '!!##~~~~===~~~~===')
>> RETURN NULL
>> -- We can't return a string larger than 4000 char's so if the len is
>> greater
>> -- then that we can return NULL as an error response.
>> IF (@.part_len > 4000)
>> RETURN NULL
>> DECLARE @.str_len int
>> DECLARE @.idx int
>> DECLARE @.pre_len int
>> SELECT @.str_len = LEN(@.initial)
>> SET @.idx = 0
>> SELECT @.pre_len = LEN(@.search_str)
>> SELECT @.idx = charindex(@.search_str, @.initial)
>> IF (@.idx = 0)
>> BEGIN
>> RETURN NULL
>> END
>> -- Declare the return variable since we are done with all other
>> returns
>> DECLARE @.return nvarchar(4000)
>> -- Now set the idx to the start of the part we want to return instead
>> of
>> -- the sart of the preceeding string value.
>> SELECT @.idx = @.idx + @.pre_len
>> IF (@.str_len < (@.idx + @.part_len))
>> BEGIN
>> -- We extend beyond the end of the string so get all we can
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx)
>> END
>> ELSE
>> BEGIN
>> -- pull just the part requested
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
>> END
>> RETURN @.return
>> END
>> GO
>>
>|||No problem...
Just be aware that this will only work if you are using non-unicode data
types (varchar, char, etc). If you switched to unicode (nvarchar, nchar,
etc) the datalength function wouldn't return the values that you are looking
for as it is actually returning the number of bytes, which could be doubled
if you are using a double byte unicode character set.
Geoff Chovaz
MCTS: SQL Server 2005
MCITP: Database Administrator
MCITP: Database Developer
<marc.l.caron@.gmail.com> wrote in message
news:1190147456.880474.200280@.g4g2000hsf.googlegroups.com...
> Excelent! Thank you. Here is the new version of the function with
> the change and other things fixed/cleaned up. DATALENGTH is what I
> needed.
> ----
> IF EXISTS (SELECT * FROM sysobjects WHERE id => OBJECT_ID(N'dbo.StringPart'))
> DROP FUNCTION dbo.StringPart
> GO
> /
> ******************************************************************************
> * StringPart will return the length of string from inside of another
> based
> * off a preceding string value. It will only return the first
> satisfying part
> * from the source string.
> * @.initial as nvarchar(4000) - the string value to pull a piece from
> * @.search_str as nvarchar(4000) - the preceeding string value to the
> part
> * desired.
> * @.part_len as int - the part of the string you wish to pull out that
> follows
> * the search string value (@.search_str)in the larger @.initial
> string.
> *
> * return nvarchar(4000) - the @.part_len number of characters
> following after
> * the first occurance of @.search_str in the @.initial string. If
> the
> * number of characters would have exceeded the length of the
> original
> * string then only the available length of characters will be
> returned.
> *****************************************************************************/
> ----
> CREATE FUNCTION dbo.StringPart (
> @.initial as nvarchar(4000),
> @.search_str as nvarchar(4000),
> @.part_len as int
> )
> RETURNS varchar(4000)
> AS
> BEGIN
> -- Check for NULL or length of 0
> IF (@.search_str IS NULL OR DATALENGTH(@.search_str) = 0)
> RETURN NULL
> -- We can't return a string larger than 4000 char's so if the len is
> greater
> -- then that we can return NULL as an error response.
> IF (@.part_len > 4000)
> RETURN NULL
> DECLARE @.idx int -- holds start index's as needed
> DECLARE @.str_len int -- length of original string
> DECLARE @.pre_len int -- length of preceeding string
> SET @.idx = 0
> -- variables are UNICODE so datalength is 2 times longer then string
> characters
> SELECT @.str_len = DATALENGTH(@.initial) / 2
> SELECT @.pre_len = DATALENGTH(@.search_str) / 2
> -- Find the preceeding text string
> SELECT @.idx = CHARINDEX(@.search_str, @.initial)
> IF (@.idx = 0)
> BEGIN
> RETURN NULL
> END
> -- Declare the return variable since we are done with all other
> returns
> DECLARE @.return nvarchar(4000)
> -- Now set the idx to the start of the part we want to return instead
> of
> -- the sart of the preceeding string value.
> SELECT @.idx = @.idx + @.pre_len
> IF (@.str_len < (@.idx + @.part_len))
> BEGIN
> -- We extend beyond the end of the string so get all we can
> SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx + 1)
> END
> ELSE
> BEGIN
> -- pull just the part requested
> SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
> END
> RETURN @.return
> END
> GO
>
> IF ('string of st' != dbo.StringPart('my big string of stuff', 'big ',
> 12))
> BEGIN
> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
> 12) = ' PRINT dbo.StringPart('my big string of stuff', 'big ', 12)
> END
> IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
> BEGIN
> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
> 2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
> END
> IF (' stuff' != dbo.StringPart('my big string of stuff', 'of', 12))
> BEGIN
> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''of'',
> 12)' PRINT dbo.StringPart('my big string of stuff', 'of', 12)
> END
> IF (' big string ' != dbo.StringPart('my big string of stuff', 'my',
> 12))
> BEGIN
> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''my'',
> 12)' PRINT dbo.StringPart('my big string of stuff', 'my', 12)
> END
>|||correct. I have the string params to the function as nvarchar's and
as such the DATALENGTH did return double the length of string. Easy
enough to deal with as the string functions will all operate on
characters not bytes it's easy to take half the length and be done
with.
marc|||No, this won't work with LEN because this function will recast its argument
recast to a string var; however, it will work for making direct equality
comparaisons:
select Case when 'abc' = 'abc ' then 1 else 0 End
select Case when Cast('abc' as varbinary(30)) = Cast('abc ' as
varbinary(30)) then 1 else 0 End
Not the best solution but if you want to make a distinction for trailing
blanks, using varbinary might be the easiest solution in many cases.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
news:OVyHNkr%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> That doesn't work for me...
> declare @.mystring varchar(30)
> set @.mystring = 'abc '
> select len(cast(@.mystring as varbinary(30)))
> 3
>
>
> --
> Geoff Chovaz
> MCTS: SQL Server 2005
> MCITP: Database Administrator
> MCITP: Database Developer
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:enPvFNj%23HHA.4612@.TK2MSFTNGP03.phx.gbl...
>> Another possibility would be to cast them to a varbinary.
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>> "Geoff Chovaz" <chovaz@.nospam.nospam> wrote in message
>> news:%23vrS4xi%23HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Try looking into the DATALENGTH function instead of LEN
>> select len('abc '), datalength('abc ')
>> 3 4
>>
>>
>> <marc.l.caron@.gmail.com> wrote in message
>> news:1190143353.158985.202430@.y42g2000hsy.googlegroups.com...
>> Here's what I've got. I have need of a function to parse out a piece
>> of data from a text string that falls after a specific piece of text.
>> Ex: Large String Value: 'my big string of stuff'
>> Key piece of data value trails: 'big '
>> # of chars to pull: 2
>> So I've created a simple function (see below)
>> The issue is that using this function in the syntax below yields the
>> wrong data due to spaces being truncated in some situations but not
>> others. So in the below test of the function I've created you can see
>> that the 2 characters immediately following 'big ' are 'st'. However
>> since I can't find an accurate way to get the length of 'big
>> ' (returns 3 instead of 4) I always end up with ' s' as the result.
>> IF ('st' != dbo.StringPart('my big string of stuff', 'big ', 2))
>> BEGIN
>> PRINT 'FAILED - dbo.StringPart(''my big string of stuff'', ''big '',
>> 2)' PRINT dbo.StringPart('my big string of stuff', 'big ', 2)
>> END
>> Also take this example. The trailing spaces are preserved as part of
>> the value but not properly processed by the length method.
>> DECLARE @.a varchar(5)
>> DECLARE @.b varchar(5)
>> SET @.a = 'a '
>> SET @.b = 'b '
>> SELECT @.a, LEN(@.a), @.b, LEN(@.b), @.a + @.b, LEN(@.a + @.b)
>> -- -- -- -- -- --
>> a 1 b 1 a b 4
>> And my last argument (although pointless) this SQL will continue until
>> int is maxed out since apparently it has no way of knowing when it is
>> at the end of a varchar value. I would expect it to fail accessing
>> index's past the length of the varchar value but it just keeps going
>> returning empty strings.
>> DECLARE @.a varchar(4)
>> SET @.a = 'a '
>> DECLARE @.idx int
>> SET @.idx = 1
>> loop:
>> BEGIN
>> SELECT SUBSTRING(@.a, @.idx, 1)
>> SELECT @.idx = @.idx + 1
>> GOTO loop
>> END
>>
>> So all that said I need a way to get my function to properly evaluate
>> a string with a trailing space. I'm out of ideas as it's quite
>> obvious MS SQL thinks it knows best. And ANSI_PADDING makes no
>> difference on variables I've verified .
>> Also
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>>
>>
>> ----
>> IF EXISTS (SELECT * FROM sysobjects WHERE id =>> OBJECT_ID(N'dbo.StringPart'))
>> DROP FUNCTION dbo.StringPart
>> GO
>> /
>> ******************************************************************************
>> * StringPart will return the length of string from inside of another
>> based
>> * off a preceding string value. It will only return the first
>> satisfying part
>> * from the source string.
>> * @.initial as nvarchar(4000) - the string value to pull a piece from
>> * @.search_str as nvarchar(4000) - the preceeding string value to the
>> part
>> * desired.
>> * @.part_len as int - the part of the string you wish to pull out that
>> follows
>> * the search string value (@.search_str)in the larger @.initial
>> string.
>> *
>> * return nvarchar(4000) - the @.part_len number of characters
>> following after
>> * the first occurance of @.search_str in the @.initial string. If
>> the
>> * number of characters would have exceeded the length of the
>> original
>> * string then only the available length of characters will be
>> returned.
>> *****************************************************************************/
>> ----
>> CREATE FUNCTION dbo.StringPart (
>> @.initial as nvarchar(4000),
>> @.search_str as nvarchar(4000),
>> @.part_len as int
>> )
>> RETURNS nvarchar(4000)
>> AS
>> BEGIN
>> -- Can't use '' as ' ' will end up evaluating to true which is stupid
>> but
>> -- none the less an alternative was needed. If one of our sources
>> is equal
>> -- to this particular sting then i'd guess our source has issues.
>> IF (ISNULL(@.search_str, '!!##~~~~===~~~~===') = '!!##~~~~===~~~~===')
>> RETURN NULL
>> -- We can't return a string larger than 4000 char's so if the len is
>> greater
>> -- then that we can return NULL as an error response.
>> IF (@.part_len > 4000)
>> RETURN NULL
>> DECLARE @.str_len int
>> DECLARE @.idx int
>> DECLARE @.pre_len int
>> SELECT @.str_len = LEN(@.initial)
>> SET @.idx = 0
>> SELECT @.pre_len = LEN(@.search_str)
>> SELECT @.idx = charindex(@.search_str, @.initial)
>> IF (@.idx = 0)
>> BEGIN
>> RETURN NULL
>> END
>> -- Declare the return variable since we are done with all other
>> returns
>> DECLARE @.return nvarchar(4000)
>> -- Now set the idx to the start of the part we want to return instead
>> of
>> -- the sart of the preceeding string value.
>> SELECT @.idx = @.idx + @.pre_len
>> IF (@.str_len < (@.idx + @.part_len))
>> BEGIN
>> -- We extend beyond the end of the string so get all we can
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.str_len - @.idx)
>> END
>> ELSE
>> BEGIN
>> -- pull just the part requested
>> SELECT @.return = SUBSTRING(@.initial, @.idx, @.part_len)
>> END
>> RETURN @.return
>> END
>> GO
>>
>>
>