Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Issues from converting from mySQL to msSQL

Posted on 2014-08-04
2
Medium Priority
?
241 Views
Last Modified: 2014-08-04
We have a query (in ColdFusion) that used to back to a mySQL server.

   select LENGTH(Subchapter_Article1) - (LENGTH(REPLACE(Subchapter_Article1,"building staff","")) ) as D_Count,Subchapter_Article1,Subchapter_Title,Part_ID,Chapter_ID,Subchapter_ID,Publish_Subchapter_Article1 from subchapters4 where Publish_Subchapter_Article1 like "%building staff%" and Part_ID != '5' and Publish_Subchapter_Article1 !='' and chapter_id in (select chapter_id from chapters4 where publish_status = 'y') ORDER BY D_Count DESC

Open in new window


when we change it to LEN instead of LENGTH

   select LEN(Subchapter_Article1) - (LEN(REPLACE(Subchapter_Article1,"building staff","")) ) as D_Count,Subchapter_Article1,Subchapter_Title,Part_ID,Chapter_ID,Subchapter_ID,Publish_Subchapter_Article1 from subchapters4 where Publish_Subchapter_Article1 like "%building staff%" and Part_ID != '5' and Publish_Subchapter_Article1 !='' and chapter_id in (select chapter_id from chapters4 where publish_status = 'y') ORDER BY D_Count DESC

Open in new window


I get the following error:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
0
Comment
Question by:digitalwise
2 Comments
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 40240340
EDIT: Wow... if I'm not mistaken, that's a very cryptic way of saying you need to use single quotes around the string literals, instead of double quotes ;-)

Just change the quotes and it works without a hitch.

SELECT
       LEN(Subchapter_Article1) - 
       (
           LEN(REPLACE(Subchapter_Article1,'building staff','')) 
       ) 
       AS D_Count
FROM  subchapters4 
ORDER BY D_Count DESC

Open in new window

0
 

Author Closing Comment

by:digitalwise
ID: 40240368
Thanks - as usual for your help!   I would be great if I could hire you for some of these projects
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question