Wildcards in SQL Server 2008 - 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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Wildcards in SQL Server 2008

Author

Topic

DB Analyst
Starting Member

8 Posts

Posted - 2011-02-07 : 16:20:48

Hi everyone,

I am new to this forum. I was not able to find an answer to my question elsewhere, so I was hoping someone would help me out.

I am trying to write a SQL query that compares a NTEXT field in one table with a list of NTEXT values in another.

For example, the smaller "reference" list may contain the 3 values

Apple
Orange
Pear

If the other larger foods table has the below words, I would like SQL Server to run a check of the above words against the below words. Then, then the words in parantheses would be grabbed by the query. The only one not grabbed would be "lemon juice". As shown:

***Apple pie***
***Cinnamon apple***
Lemon juice
***orangeade***
***Appletini***

I have the query written something like this:

SELECT [FRUIT_NAME]
FROM [FRUITS].[dbo].[TABLE 1] tbl1
JOIN [FRUITS].[dbo].[TABLE 2] tbl2
ON tbl1.[FRUIT_FOODS_] LIKE '%' & tbl2.[FRUIT_REFERENCES] & '%'

However, SQL Server says there is a problem. Specifically, it says that data types VARCHAR and NTEXT are incompatible with the '&' operator.

I would appreciate any help you can give me.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-07 : 16:23:41

SELECT [FRUIT_NAME]
FROM [FRUITS].[dbo].[TABLE 1] tbl1
JOIN [FRUITS].[dbo].[TABLE 2] tbl2
ON tbl1.[FRUIT_FOODS_] LIKE '%' + tbl2.[FRUIT_REFERENCES] + '%'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DB Analyst
Starting Member

8 Posts

Posted - 2011-02-07 : 16:37:16

Hmmm...when I put in the plus symbols, it now says this: The data types varchar and ntext are incompatible in the add operator.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-07 : 17:15:42

You're using the wrong datatype. NTEXT is for binary data, and you're storing regular character data it looks like.

Ought to be using NVARCHAR(xxx).

Anyway, as the error says, concatenation operator isn't valid with NTEXT.

Convert it to NVARCHAR(max)


 SELECT [FRUIT_NAME]
FROM [FRUITS].[dbo].[TABLE 1] tbl1
JOIN [FRUITS].[dbo].[TABLE 2] tbl2
ON Convert(varchar(max), tbl1.[FRUIT_FOODS_]) LIKE '%' + Convert(varchar(max), tbl2.[FRUIT_REFERENCES]) + '%'

DB Analyst
Starting Member

8 Posts

Posted - 2011-02-08 : 14:52:04

Thanks for the help, Russell. I've followed your suggestion and noticed there was also another problem with my database. This has now been fixed.

It is now working. Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-09 : 01:45:46

Glad to hear it. You're Welcome

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-09 : 06:41:25

quote:
Originally posted by russell

You're using the wrong datatype. NTEXT is for binary data, and you're storing regular character data it looks like.

I'm sorry Russel, and I'm sure it's just a simple mixup, but I can't leave this quote "unchallenged" :) NTEXT is for unicode data, not binary.

And Mr DB Analyst: If you're using ntext in a 2008 database you should change the data type to nvarchar(max). Ntext is a deprecated datatype and is not guaranteed to work in future versions of sql serevr. n/varchar(max) also has better functionality in the sense that it behaves like a normal n/varchar datatype.

- Lumbago
My blog-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources