SQL 2005: using LIKE with column of type Text

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

SQL 2005: using LIKE with column of type Text

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-09-19 : 16:06:19

Hi,
How do we use like when we have a column of type Text

Select * from where myColumn LIKE 'prefix%'

where myColumn is of type Text in SQL server 2005

Thanks.
__________________

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-19 : 16:08:46

1) You can alter the datatype to VARCHAR(MAX) and continue working the way you are used to
2) PATINDEX and CHARINDEX works equal to LIKE

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-19 : 16:10:08

for that
Select * from tbl where substring(myColumn,1,6) = 'prefix'
more generally
Select * from tbl where patindex('%prefix%', myColumn) <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-19 : 16:27:30

quote:
Originally posted by rtutus

Hi,
How do we use like when we have a column of type Text

Select * from where myColumn LIKE 'prefix%'

where myColumn is of type Text in SQL server 2005

Thanks.
__________________

The select statement you posted should work fine.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources