string length

[tuzojazz]

Hi:

suppose I have this table

CUSTOMERS
-----------------
Number Name
-----------------
1 John
2 Gabriel
3 George
4 Ken

I need to make a query to get customers where the Name length is equal or greater than 6

Number Name
-----------------
2 Gabriel
3 George


How Can I make this query or/and where can I find information about this kind of query?

Thanks!!

 

[r937]

select ... from ... where datalength(name) >= 6

r937.com | rudy.ca

 

[Fahtrim]

What platform is the datalength function valid for?

But that is the correct idea fairly easy, but the performance on a large table would probably have to be a full table scan. (In Oracle you can create function based indexes.)

And depending on the datatype you may have to perform a TRIM operation on the name field to ignore leading and trailing blanks.

 

[JarlH]

The ANSI/ISO function is CHAR_LENGTH, and it works for both character ("ASCII") and national character ("Unicode") data types.

And as Fahtrim wrote, both leading and trailing blanks are included in the result.

 

[r937]

aw, shoot

sorry, i meant character_length

i don't know where i got datalength from

r937.com | rudy.ca

 

[tuzojazz]

Hi:

Thanks but neither datalength nor character_length work in access.

Is ther a function that works in access?

 

[Fahtrim]

Len Function



Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len(string | varname)

The Len function syntax has these parts:

Part Description
string Any valid string expression. If string contains Null, Null is returned.
Varname Any valid variable name. If varname contains Null, Null is returned. If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.


Remarks

One (and only one) of the two possible arguments must be specified. With user-defined types, Len returns the size as it will be written to the file.

Note Use the LenB function with byte data contained in a string, as in double-byte character set (DBCS) languages. Instead of returning the number of characters in a string, LenB returns the number of bytes used to represent that string. With user-defined types, LenB returns the in-memory size, including any padding between elements. For sample code that uses LenB, see the second example in the example topic.

Note Len may not be able to determine the actual number of storage bytes required when used with variable-length strings in user-defined data types.

 

[r937]

know why i stumbled between datalength and character_length? because this is the ANSI SQL forum, and i seldom use the ANSI SQL functions

if you had said Access, this thread would've been over ages ago

as you have discovered, not every database system supports ANSI SQL functions, and instead have their own functions

i know them better than the ANSI SQL functions ;-)




r937.com | rudy.ca

 

[tuzojazz]

Thanks!!

Len function works in access!!

you are right r937,not every database system supports ANSI SQL functions and I had said Access on my first post.

 

[Golom]

... not every database system supports ANSI SQL functions ...

Is there any RDBMS that has faithfully implemented ANSI SQL?

 

[r937]

from what i've heard, mimer does

haven't tried it myself, though

r937.com | rudy.ca