How to find records with length greater than 17

am 21.04.2008 21:07:07 von ciojr

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

Re: How to find records with length greater than 17

am 21.04.2008 21:36:18 von 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

Re: How to find records with length greater than 17

am 22.04.2008 08:23:33 von Gabe Garza

"Plamen Ratchev" 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

Re: How to find records with length greater than 17

am 22.04.2008 14:34:56 von 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

Re: How to find records with length greater than 17

am 22.04.2008 18:15:08 von ciojr

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

Re: How to find records with length greater than 17

am 23.04.2008 18:46:03 von Gert-Jan Strik

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