How to Find Fields Containing Only One String Character?

[txdave35]

Hey guys,

I am writing a query that returns an employee name field called F_NAME. I need my query to only return employees that have a single letter for their first name. Can someone tell me how to do this? I'm thinking it's some kind of length function, but not finding the solution today. Thanks!

 

[SQLBill]

Try this:

SELECT F_NAME
FROM mytable
WHERE DATALENGTH(F_NAME) = 1
GO

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bborissov]

DECLARE @Temp TABLE (Fld1 CHAR(10))
INSERT INTO @Temp VALUES ('A        ')
INSERT INTO @Temp VALUES ('AAAAAA')
INSERT INTO @Temp VALUES ('A a   ')

SELECT * FROM @Temp WHERE LEN(Fld1) = 1

Borislav Borissov
VFP9 SP2, SQL Server

 

[wbodger]

Go with what SQLBill said if you want to count spaces, what bborissov if you do not want to count spaces. ie
select len('one ')=3
select datalength('one ')=4

datalength tells you how many bytes the data is using while len gives you the actual length of the string.

wb

 

[txdave35]

Thanks guys. Worked like a charm!

 

[gmmastros]

Datalength will double the character count if you are using unicode data. Ex:

[tt]Select DataLength(N'A') -- Returns 2[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom