SELECT * FROM field WHERE there are more than 6 characters

Question

Post reply

SELECT * FROM field WHERE there are more than 6 characters

Erin.

SSCertifiable

Points: 5237
More actions
January 13, 2009 at 10:22 am

#125580

I am trying to find data in a particular field where there are more than 6 characters. The field is varchar(10). How would I select the data that has more than 6 characters?
Example data:
FIRST
CORP
SUNSHINE
SURF
ANDERSON
My query should return:
SUNSHINE
ANDERSON

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Maxim Picard SSCrazy Points: 2052 More actions · Answer 1

select column from table where len(column) > 6

That should do it!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

As long as the "characters" being sought don't include blanks - I agree with Maxim.

If you might be looking for trailing blanks - then concatenate a . and look for len of 7.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chip Smithson-334408 SSC Enthusiast Points: 124 More actions · Answer 3

Another way to lose any leading or trailing spaces....

select column from table where len(ltrim(rtrim(column))) > 6

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Chip Smithson (1/13/2009)
Another way to lose any leading or trailing spaces....
select column from table where len(ltrim(rtrim(column))) > 6

The rtrim isn't necessary since len will take care of that for you. Again - it comes down to defining what you are counting.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Chip Smithson-334408 SSC Enthusiast Points: 124 More actions · Answer 5

Matt,

yes my bad. We have standardized using the DATALENGTH function which takes into account all bytes thus having to trim trailing spaces, if so desired.