OT: how to do this ' '+columnName+' ' with data type of TEXT?

[Rob Meade]

Hi all,

As above really...I have the following SQL statement (I've dumped this from
my ASP page running it so it has some values, and I've moved it around to
try and prevent wrapping)...

SELECT
WebsiteID, WebsiteName, WebsiteDesc, WebsiteURL,
WebsiteMetaKeywords, WebsiteMetaDescription, 0 AS Relevance

FROM view_Websites

WHERE
(
' ' + WebsiteName + ' ' LIKE '% para% %' OR ' ' + WebsiteName + ' '
LIKE '% companies %'
OR ' ' + WebsiteName + ' ' LIKE '% ir35 %'
)

OR

(
' ' + WebsiteDesc + ' ' LIKE '% para% %' OR ' ' + WebsiteDesc + ' '
LIKE '% companies %'
OR ' ' + WebsiteDesc + ' ' LIKE '% ir35 %'
)

OR

(
' ' + WebsiteMetaKeywords + ' ' LIKE '% para% %'
OR ' ' + WebsiteMetaKeywords + ' ' LIKE '% companies %'
OR ' ' + WebsiteMetaKeywords + ' ' LIKE '% ir35 %'
)

OR

(
' ' + WebsiteMetaDescription + ' ' LIKE '% para% %'
OR ' ' + WebsiteMetaDescription + ' ' LIKE '% companies %'
OR ' ' + WebsiteMetaDescription + ' ' LIKE '% ir35 %'
)

AND WebsiteID = '1'



My problem is that I have at least one datatype of TEXT which doesn't seem
to happy with me trying to concatenate a space either end of the field.

I've tried putting it in brackets, I've tried using & instead of + but still
no joy...can some one please advise.

Many thanks for your reply, sorry if me reformatting the above hasn't
helped.

Regards

Rob

 

[Mark Schupp]

Response.write the completed SQL Statement out and post it.

If you are using VBScript you might want to get in the habit of using & for
string concatenation but I would not expect it to make a difference in your
example.

 

[Rob Meade]

...

Hi Mark, thanks for your reply.

Response.write the completed SQL Statement out and post it.

I just did, I reformatted it a bit so it was easier to read - but if you
prefer...

SELECT WebsiteID, WebsiteName, WebsiteDesc, WebsiteURL, WebsiteMetaKeywords,
WebsiteMetaDescription, 0 AS Relevance FROM view_Websites WHERE (' ' +
WebsiteName + ' ' LIKE '% para% %' OR ' ' + WebsiteName + ' ' LIKE '%
companies %' OR ' ' + WebsiteName + ' ' LIKE '% ir35 %' ) OR (' ' +
WebsiteDesc + ' ' LIKE '% para% %' OR ' ' + WebsiteDesc + ' ' LIKE '%
companies %' OR ' ' + WebsiteDesc + ' ' LIKE '% ir35 %' ) OR (' ' +
WebsiteMetaKeywords + ' ' LIKE '% para% %' OR ' ' + WebsiteMetaKeywords + '
' LIKE '% companies %' OR ' ' + WebsiteMetaKeywords + ' ' LIKE '% ir35 %' )
OR (' ' + WebsiteMetaDescription + ' ' LIKE '% para% %' OR ' ' +
WebsiteMetaDescription + ' ' LIKE '% companies %' OR ' ' +
WebsiteMetaDescription + ' ' LIKE '% ir35 %' ) AND WebsiteID = '1'

If you are using VBScript you might want to get in the habit of using & for
string concatenation but I would not expect it to make a difference in your
example.

But this is a SQL statement, not VB string concatenation is it not? I
believe the correct operator in SQL is the + character?

Regards

Rob

 

[Ryan N.]

Have a look at http://www.aspfaq.com/2188 ...

 

[Rob Meade]

...

Have a look at http://www.aspfaq.com/2188 ...

Hello Ryan, thanks for your reply.

Not trying to be rude, please dont think so - but I dont see, unless I
missed something, how that relates to my problem?

I should have mentioned perhaps that I am using SQL Server, not Access.

Regards

Rob

 

[Aaron Bertrand - MVP]

You can't use string contatenation (and many other string operators) on a
TEXT column.

Not sure what you're gaining here, unless you're concerned that the company
name might appear at the very beginning or very end of the column data.

If so, you can say

WHERE column LIKE 'foo %' or column LIKE '% foo' or column LIKE '% foo %'

Or, you could use full text search, which deals with "similar words" and
"complete words only" issues for you. I take it though, since this has been
suggested frequently but you're still using LIKE, that you don't like that
idea (no pun intended).