Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon, which comes first ob_start or session, wwwxxx/58/2010

Links

XODOX
Impressum

#1: How to find records with length greater than 17

Posted on 2008-04-21 21:07:07 by ciojr

how do i write sql statment to find records with name greater than 17
characters.

Report this message

#2: Re: How to find records with length greater than 17

Posted on 2008-04-21 21:36:18 by Plamen Ratchev

The LEN function returns the number of characters excluding trailing blanks:

SELECT namecol
FROM Foo
WHERE LEN(namecol) > 17;

The DATALENGTH function returns the number of bytes.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Report this message

#3: Re: How to find records with length greater than 17

Posted on 2008-04-22 08:23:33 by Gabe Garza

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:t9ednRsfVu9McZHVnZ2dnUVZ_jSdnZ2d@speakeasy.net...
> The LEN function returns the number of characters excluding trailing
> blanks:
>
> SELECT namecol
> FROM Foo
> WHERE LEN(namecol) > 17;
>
> The DATALENGTH function returns the number of bytes.
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

Wouldnt it matter if the field was in unicode then? What do you do if you
want to include the spaces?

I dont really want to know I just thought it weird that len would behave
like that...(Im an SQL server noob)

John Sheppard

Report this message

#4: Re: How to find records with length greater than 17

Posted on 2008-04-22 14:34:56 by Plamen Ratchev

There is no difference when you use LEN with Unicode column. If you need to
include the spaces you can use DATALENGTH. The only considerations is that
it returns number of bytes, so for NVARCHAR/NCHAR/NTEXT will return double
the length.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Report this message

#5: Re: How to find records with length greater than 17

Posted on 2008-04-22 18:15:08 by ciojr

On Apr 21, 3:36=A0pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> The LEN function returns the number of characters excluding trailing blank=
s:
>
> SELECT namecol
> FROM Foo
> WHERE LEN(namecol) >17;
>
> The DATALENGTH function returns the number of bytes.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Right - i dont want to include spaces.
I want to check where the first word is greater than 17

Report this message

#6: Re: How to find records with length greater than 17

Posted on 2008-04-23 18:46:03 by Gert-Jan Strik

ciojr@yahoo.com wrote:
>
> On Apr 21, 3:36 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> > The LEN function returns the number of characters excluding trailing blanks:
> >
> > SELECT namecol
> > FROM Foo
> > WHERE LEN(namecol) >17;
> >
> > The DATALENGTH function returns the number of bytes.
> >
> > HTH,
> >
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Right - i dont want to include spaces.
> I want to check where the first word is greater than 17

It won't do that. For example, the query above will also select the row
where namecol has a value of 'John Smithersonson'

So if Plamen's solution does not do what you want, then please some
example data and desired output.

--
Gert-Jan

Report this message