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

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

"Plamen Ratchev" <Plamen [at] SQLStudio.com> wrote in message
news:t9ednRsfVu9McZHVnZ2dnUVZ_jSdnZ2d [at] 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

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

On Apr 21, 3:36=A0pm, "Plamen Ratchev" <Pla... [at] 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

ciojr [at] yahoo.com wrote:
>
> On Apr 21, 3:36 pm, "Plamen Ratchev" <Pla... [at] 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