How to find records with length greater than 17

[ciojr]

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

[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

[John Sheppard]

"Plamen Ratchev" <Pl****@SQLStudio.comwrote in message
news:t9******************************@speakeasy.ne t...

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

[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

[ciojr]

On Apr 21, 3:36*pm, "Plamen Ratchev" <[e-mail]:

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

[Gert-Jan Strik]

ci***@yahoo.com wrote:

>
On Apr 21, 3:36 pm, "Plamen Ratchev" <[e-mail]:

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