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
>>
>>
>