Select based on filed length?

Question

Post reply

Select based on filed length?

WaIIy

Ten Centuries

Points: 1327
More actions
November 7, 2008 at 10:09 am

#73253

Hi all,
Relative newbie here in the world of SQL.
How can I do a select on a text as well as an integer column and narrow it down to specific lengths for the return?
i.e. I want to see everything with more than or less than 12 characters?
Thanks much!
Wally
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

When you say text, do you really mean a text datatype or is it a varchar/char data type?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

WaIIy Ten Centuries Points: 1327 More actions · Answer 2

Sorry,

varchar(50)

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Wally

Are you sure the data type for the column is TEXT, or is it VARCHAR, or CHAR? I presume that you are not checking for more or less than x number of digits in the column whose data type is INT, or am I wrong?

If the column has a data type of TEXT or NTEXT I suggest you read up on the DATALENGTH function in Books on Line (BOL) Then you could compose your select statement somewhat like

SELECT X, SomeStuff FROM dbo.tableAA

WHERE DATALENGTH(SomeStuff) > 12

or for VARCHAR you might use something like

SELECT SomeStuff, LEN(Somestuff) AS 'Len someStuff' FROM Dbo.TableAA WHERE LEN(Somestuff) > 12

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

John Rowan SSC Guru Points: 56440 More actions · Answer 4

In that case, use LEN:

DECLARE @Table TABLE (Col1 int, Col2 varchar(50))

INSERT INTO @Table

SELECT 1, '123234345' UNION ALL

SELECT 2, '123456789124578'

SELECT *

FROM @Table

WHERE LEN(Col2) < 12

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

WaIIy Ten Centuries Points: 1327 More actions · Answer 5

John Rowan (11/7/2008)
In that case, use LEN:
DECLARE @Table TABLE (Col1 int, Col2 varchar(50))
INSERT INTO @Table
SELECT 1, '123234345' UNION ALL
SELECT 2, '123456789124578'
SELECT *
FROM @Table
WHERE LEN(Col2) < 12

Perfect!

Thanks!

Wally

[font="Verdana"]-- Wally
-- Reluctant DBA[/font]