CASE statement using wildcard

im trying to use wildcard in CASE statement with no luck.

im use this:

SELECT CASE tb001
WHEN '%price%' then 'Price'
ELSE 'No price'

the colum is varchar

The expected result would be 'Price' in every row that contains price (with wildcard around) but every line shows 'No price'
Everything works fine if i don't use wildcard and put in the whole string.

thx in advance //MrTry (not tested)
SELECT
CASE
WHEN tb001 like '%price%' then 'Price'
ELSE 'No price'
END
FROM ...|||works like charm... thx alot

Cascading Parameter Defaults

I've had good luck with driving reports with cascading parameters and
parameters with defaults. So far though, I've been unable to combine these
concepts.
Using the example mentioned in BOL, I'd like to have a division parameter
choice drive a department parameter dropdown. When the department dropdown
is populated, is there any way to specify a default value? So far, my
attempts to do so have failed - with no default being chosen and no errors
appearing.
Does anyone know how I might be able to accomplish this?I was able to use defaults with cascading parms. In my scenario, I wanted
the user to choose the division (I'm actually using something else), but then
have the option to choose the department. Basically, the report would show
data for the entire division if the department parm wasn't choosen.
The only way I could get this to work was to use a default for the
department parm. I set the default to "non-queried" and the formula equal to
="" (and empty string). Also, the department parm is checked off to allow
null and blank values.
At first this didn't work because the default value must be a valid value in
the department dataset that populates the dropdown. My fix was to union in
a select statement tothe dataset with the default empty string value. Here's
that sql:
SELECT '' DeptID, '' DeptName
UNION ALL
SELECT DeptID, DeptName FROM DeptTable
WHERE DivID = @.DivID
ORDER BY DeptName
Now, when the division dropdown is choosen, the cascade parm populates the
department dropdown. But since the default is set to "", and "" is a valid
choice in the recordset - RS is happy.
The only thing else I had to do was have the actual report dataset know how
to handle the department as an empty string. No biggie.
Mike, to apply this to your scenario, I think the key is making sure the
default value is always in the dataset for the dept dropdown.
Hope this helps, Frank

Carriage Returns in Data

I have a ntext field of data. I was trying to use the REPLACE function to
change the carriage returns to spaces, but have not any luck.
Can anyone make any suggestions?
Thank you,
JLFlemingYou don't need the text within <> -- it is just an example to show that
things are working as expected.
Here is an example:
create table #foo (col1 varchar(20))
insert into #foo values ('test')
insert into #foo values ('test
more')
select col1 from #foo
select REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>')
from #foo
Keith
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||You will have write a procedure that loops 8000 character chunks of the data
doing the replace.
Thomas
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||I missed the ntext bit the first time I read your post.
Keith
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMF47mHYFHA.4036@.tk2msftngp13.phx.gbl...
> You don't need the text within <> -- it is just an example to show that
> things are working as expected.
> Here is an example:
> create table #foo (col1 varchar(20))
> insert into #foo values ('test')
> insert into #foo values ('test
> more')
> select col1 from #foo
> select
> REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>') from
> #foo
>
> --
> Keith
>
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>|||The best way to do this is outside of SQL Server. Text data is a beast in
SQL Server 2000 and earlier to deal with in SQL. The chunking idea given by
Thomas is feasible, but you have to be careful about your search value
crossing the chunk boundry.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!
Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!

Carriage Returns become Question Marks in SQL

Hi all,
I tried the ASP .NET forum with no luck on this one. Maybe someone
here will know the answer.
I have an old ASP .NET 1.1 application that I haven't had time to
rebuild with .NET 2.0. No changes have been made to the application
or
the SQL Server the application uses. I have a web form where users
can
type multiple lines of text, and it is entered into an SQL database
(SQL 2000 Enterprise) into a column with a datatype of "text".
Recently (it seems out of nowhere), If my users enter a carriage
return into the webform, it becomes a question mark in the sql
database. It's really a bit funny, but also annoying, lol. Does
anyone
have any idea why this might be happening?
To clarify. I'm typing text into a multiline textbox. For every place
I hit the return key, it is converted into a question mark in SQL.
I'm
using standard .net sql insert commands, and I don't parse or modify
the text string in anyway. The question marks show whether I load the
data back into my webform, or even if I do a SQL query via Query
Analyzer. I'm stumped.
Thanks so much!Can you post an example insert string (e.g. from the debug window of your
app)? Does it still produce ?s if you paste that string into Query
Analyzer, and execute it manually? If so, take the string, and do this:
DECLARE @.str NVARCHAR(MAX);
SET @.str = 'INSERT string here...';
DECLARE @.i INT;
SET @.i = 1;
WHILE @.i <= LEN(@.str)
BEGIN
PRINT ASCII(SUBSTRING(@.str, @.i, 1));
SET @.i = @.i + 1;
END
My guess is that .NET is injecting non-printing characters and/or not
producing correct CR/LF pairs.
<mattdaddym@.gmail.com> wrote in message
news:79ba1e43-dadf-4551-b6e5-a810cfeaf231@.i3g2000hsf.googlegroups.com...
> Hi all,
> I tried the ASP .NET forum with no luck on this one. Maybe someone
> here will know the answer.
> I have an old ASP .NET 1.1 application that I haven't had time to
> rebuild with .NET 2.0. No changes have been made to the application
> or
> the SQL Server the application uses. I have a web form where users
> can
> type multiple lines of text, and it is entered into an SQL database
> (SQL 2000 Enterprise) into a column with a datatype of "text".
> Recently (it seems out of nowhere), If my users enter a carriage
> return into the webform, it becomes a question mark in the sql
> database. It's really a bit funny, but also annoying, lol. Does
> anyone
> have any idea why this might be happening?
>
> To clarify. I'm typing text into a multiline textbox. For every place
> I hit the return key, it is converted into a question mark in SQL.
> I'm
> using standard .net sql insert commands, and I don't parse or modify
> the text string in anyway. The question marks show whether I load the
> data back into my webform, or even if I do a SQL query via Query
> Analyzer. I'm stumped.
>
> Thanks so much!