Need distinct rows from table based on (col1) only - overall required multiple c

Need rows of specific table having distinct col1, but along with col1 I also need col2,col3, .... But distinct is required on only col1.

I tried ---
Select col1, col2, ... , coln from table1 where ( select distinct col1 from table1)

But result contain repetitive col1 rows due to wrong syntax of mine. Also tried exist, union, right join but failed finally ....

give your some site:
one is mser.net,another is msdnx.com,search by google in the site....

Need Assistance with Dynamic Properties and FTP Task

Hello,

I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:

CDNSC.CDNSC.SC00015.04012007

The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.

My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.

What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?

Thank you for your help!

cdun2On Apr 4, 5:35 am, "cdun2" <ChrisDunnM...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:
>
CDNSC.CDNSC.SC00015.04012007
>
The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.
>
My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.
>
What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?
>
Thank you for your help!
>
cdun2

I wound up creating a script solution that was pretty straight
forward. I basically set up string variables using DATE to get the
data portion of the file name, concatenated that to the first part of
the filename, and set the whole string equal to the SourceFileName of
the FTP Task. It seems to work fine.

cdun2sql

Need a way to switch specific data from columns

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort.

If you have an ID column all you would need to do is check if there are multiple records (count(*)> 1). if so keep the first one (min(id) or whichever you choose), delete the rest, get the two values into local variables and update the record in hand.
if you already made an attempt post some code and we can help you out.

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

NEAR operator

What is the specific function of NEAR?
I'm indexing across 3 text fields and expect hits for 'white NEAR black' and
only getting a hit when the two words are in the same field, but not if they
are spread across two of the fields.
How NEAR does the other word have to be?
Nearness is reflected in rank. The closer together two words are the higher
the rank everything else being equal.
Nearness is calculated on a per column basis, it cannot look across columns.
FreeText factors nearness into rank (everything else being equal) and can
look across columns. FreeText ignores the near operator however, as it is
calcuated in the rank.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> What is the specific function of NEAR?
> I'm indexing across 3 text fields and expect hits for 'white NEAR black'
and
> only getting a hit when the two words are in the same field, but not if
they
> are spread across two of the fields.
> How NEAR does the other word have to be?
>
|||Very helpful. I had found that using Freetext instead of Contains was
getting me more results. Thanks Hilary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Op2bxxboEHA.1576@.TK2MSFTNGP12.phx.gbl...
> Nearness is reflected in rank. The closer together two words are the
higher
> the rank everything else being equal.
> Nearness is calculated on a per column basis, it cannot look across
columns.
> FreeText factors nearness into rank (everything else being equal) and can
> look across columns. FreeText ignores the near operator however, as it is
> calcuated in the rank.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
> news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> and
> they
>