I'm trying to add this as one of my columns:

REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '<br>')| CHAR(10)| '<br>')| '"'| 'in')+'Location: '+IF(dbo.new_truck.ditl_Inventory=True,'Vancouver','Edmonton')

But after executing the SELECT, SQL Server changes it to this: REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '<br>')| CHAR(10)| '<br>')| '"'| 'in') + 'Location: ' + 'IF(dbo.new_truck.ditl_Inventory=True,[Edmonton],[Vancouver])'

It appears that I cannot concatenate a function with a string, with another function. If I take everything after "Location:" out, of course, it works. But without the location :)

Any other way to accomplish this? I could write this into the PHP code that handles the output of this query, but thought I'd try this first

Thanks for your help!

  • 2
    What do you mean "SQL Server changes it"? Are you using some graphical query designer? Please stop doing that; write your T-SQL in a normal query window. – Aaron Bertrand Apr 15 '15 at 18:17
  • Also, inline IF, | as a concatenation operator, and Boolean expressions like true are not concepts that work in SQL Server. Is this actually Access or some other database platform? – Aaron Bertrand Apr 15 '15 at 18:18
  • This is in SQL Server Management Studio. The original field was just REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '<br>')| CHAR(10)| '<br>')| '"'| 'in') Which works just fine. I'm just trying to add on the "Location: " part to the end – JustinCredible Apr 15 '15 at 18:22
  • 1
    So first step is to stop using the designer, because that code will never work in SQL Server anyway - the fact that it allows it in any form is a testament to the quality of the designer. Next step is to use Transact-SQL constructs that actually make sense - | is not a concatenation operator, for example, you use + or , (tough to tell the intention of the nested replace calls). – Aaron Bertrand Apr 15 '15 at 18:45
  • 1
    Also, how exactly do you find that the visual designers make it easier to build/debug your queries than just writing them out? If anything, the visual designers make it harder to write queries because they re-format your code, have a variety of bugs, lack support for some query constructs, and produce vague error messages. I guess on the plus side you can check boxes instead of typing column names and pick data types from drop-downs... – Aaron Bertrand Apr 15 '15 at 19:07

I have instead edited my PHP code that handles this data:

//Show the location in the truck description if ($mainrow[24] == "0") { $mainrow[16] = $mainrow[16] . " Location: Edmonton"; } else if ($mainrow[24]=="1") { $mainrow[16] = $mainrow[16] . " Location: Vancouver"; }

where $mainrow[24] is the "Inventory" column, and $mainrow[16] is the Description column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.