Using Len function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Len function

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 10:54:39

Hi everyone,

I am trying to return the personal information of those customers who have id numbers greater than or equal to 10.
Oh, and the ID field is a string.
It tried the following


SELECT id
       ,firstname
       ,lastname
from customer
where len(id) >= 10

But it isnt right.

Do u guys have any ideas.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 10:56:24

as your reqmnt should it be?


SELECT id
       ,firstname
       ,lastname
from customer
where id >= 10

it says who have id numbers greater than or equal to 10 not who have length of id greater than or equal to 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 11:05:32

wow I think that worked. Why did it? I don't understand?

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 11:09:57

Okay I think I spoke too soon because ids with 6 digits ids are being returned.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 11:11:43

Can you show some sample data from 'id' field..and tell us which ones you expect in the output?

EDIT : And also the datatype of the 'id' field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:17:39

quote:
Originally posted by velvettiger

Okay I think I spoke too soon because ids with 6 digits ids are being returned.

can you restate what your exact requirement is?

is it

return the personal information of those customers who have id numbers greater than or equal to 10

or

return the personal information of those customers who have length of id numbers greater than or equal to 10.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 11:22:37

Firstly, i think i should mention im using access 2000.
Okay so the id field is text.
Below is a sample of the actual results set. Could it be possible that extra spaces are being included behind each id?
sample data


ID             Name        
20042485       Amanda Morris  (Shouldnt be in the list)
2003602687     John Payne      (Should be in list)
95614143       Mary Amos       (shouldnt be in the list)
200694852390   Sherry Tull      should be in list
20043785       Aronda Gill      Shouldnt be in list

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:24:58

quote:
Originally posted by velvettiger

Firstly, i think i should mention im using access 2000.
Okay so the id field is text.
Below is a sample of the actual results set. Could it be possible that extra spaces are being included behind each id?
sample data

ID Name
20042485 Amanda Morris (Shouldnt be in the list)
2003602687 John Payne (Should be in list)
95614143 Mary Amos (shouldnt be in the list)
200694852390 Sherry Tull should be in list
20043785 Aronda Gill Shouldnt be in list

then I should say you're in wrong forum. Please post it under Access forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 11:27:37

But I am using sql to right the query. Am I still in the wrong forum?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 11:34:57

This forum is dedicated to MS SQL server questions...Though there are people who can help you with Access here...you might be able to find quick solutions in a dedicated Access forum.

velvettiger
Posting Yak Master

115 Posts

Posted - 2010-03-12 : 12:58:04

ok thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 02:58:24

SELECT columns FROM your_table
WHERE LEN(TRIM(ID))>=10

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources