August 4, 2011 at 11:24 am
I need to have a function that reports out '≥ '+[report_result_limit] in to a text field when a certain condition is met.
If I enter in
when detect_flag ='G' then coalesce('>= '+[report_result_limit],'?') and the condition is met, I get "=[report_result_limit]"
Is there anyway to have SQL Server report out this symbol? Is this merely a matter of changing the accepting field to a Unicode text data type?
Thanks in advance
sadvani
August 4, 2011 at 12:33 pm
The > is most likely exported by SQL server. It's probably one of the layers above that which filters the text from your output. You haven't however given us any information on what software you're feeding SQL server's output into, so I can't do much more than guessing like I do right now.
August 4, 2011 at 12:43 pm
RP -
Thanks -
I realize now, looking at the code window, I had copied in the '=' symbol as I wanted it , but it looks like it got changed.
Anyway, when I have that symbol, what exports upon running the query "=" in SQL Server and the database application EQuIS, which is based in .Net (I didn't develop that application). I assumed that if what comes out of SQL Server matches the application, then SQL is determining the output.
When I save the .sql file, I get an error about the symbol not being available. If I ignore that message, the next time I open the file, that symbol is changed to '>'
Not sure I am giving you what you needed to go further.
sadvani
August 4, 2011 at 1:24 pm
Ah, then that's a different problem than I assumed it was: I was thinking you wanted the "greater than" symbol in your output, but found that it got filtered out in an end-report.
Instead, you have a "non-standard character", i.e. the "greater than or equal"-symbol, that you include in your query because you want to see that literal text in your output. However that character disappears when you save the query and/or when you execute the query.
There are many things to keep in mind when using non-standard characters in SQL queries. I don't pretend knowing all about it, but some things I can give you:
- You need to make sure the file you store the query in, is stored as a unicode file (i.e. it is stored as double byte characters). You can do this from the save-as dialog occasionally, or you can change it in the SSMS options window. The 'Save'-button has a small triangle to it's right side. Click it and you can select 'Save with encoding...'. The window that opens has a drop-down "Encoding". Click it and make sure that "Unicode (UTF-8 with signature)" is selected. Then save your query file under another name or you can try to save over the original file. Now the non-standard character should no longer disappear when you save the file and later re-open it.
- Next, to avoid that the character is filtered out/messed up by SQL server when executing the query, you need to make sure that SQL server stores the string in double byte columns and variables only. A double byte constant string (i.e. any fixed text in your query) is denoted by putting a capital N in front of the string. For example, select 'test' returns a constant string stored in single bytes, whereas select N'test' returns a constant string in double byte characters. The first will loose any characters not available in the server or the client's active codepage, the 2nd will enable you to show many more (but still not all) characters.
- When working with string values with non-standard characters in them, you need to make sure all columns and all variables that the values is stored in is of the double byte type. i.e. You need to make sure that they are all declared as types nchar(x), nvarchar(x) or ntext and not char(x), varchar(x) or text.
Hope this helps you.
Richard
August 4, 2011 at 3:46 pm
Richard -
This is extremely helpful - thanks. I will try this and let you know how it works!
August 5, 2011 at 11:20 am
Richard -
On the SQL side, at least, this is working. If it doesn't get passed on to the application side, I bother the developers. Thanks for all your help.
S
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply