Question about LIKE and wildcard operator (%)

Hi all,
Given the following code:
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
Why do all 3 statements match successfully?
I only want Test1 and Test2 to match and Test3 to fail...
My end code will be something like (pardon the pun):
SELECT * FROM Table T
WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
without the square brackets.
Regards,
Alextry now ;-)
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[[STRING1]]%' THEN 'yes' ELSE 'no' END AS
Test1,
CASE WHEN @.param LIKE '%[[STRING2]]%' THEN 'yes' ELSE 'no' END AS
Test2,
CASE WHEN @.param LIKE '%[[STRING3]]%' THEN 'yes' ELSE 'no' END AS
Test3
you have to escape the brackets
http://sqlservercode.blogspot.com/|||Right... nevermind!!
This is one of those D'oh!!! moments (banging head on the desk as I type
this).
I just realised that [] are used for wildcard character matching...
Changed it to using {} and it's all fine now.
Alex
"Alex" <nospam@.hotmail.com> wrote in message
news:OoPHLqRSGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Given the following code:
> DECLARE @.param nvarchar(100)
> SELECT @.param = '[STRING1][STRING2]'
> SELECT
> CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
> CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
> CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
> Why do all 3 statements match successfully?
> I only want Test1 and Test2 to match and Test3 to fail...
> My end code will be something like (pardon the pun):
> SELECT * FROM Table T
> WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
> ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
> without the square brackets.
> Regards,
> Alex
>
>|||Because square brackets are delimiters in wildcard searches; they
indicate that the search "Matches any single character within the
specified range or set that is specified inside the square brackets.".
So in your test, you are asking the optimizer if @.param contains any of
the characters S, T, R, I, N, G, (1-3) which, of course, meets all
conditions of your tests.
You'll either need to ESCAPE the square brackets, or don't use them.
Stu

Question

In Oracle, you can write a statement that uses the "&" as a variable,

For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.

How do you do this in transact SQL for SQL Server?

Thanks

Lystrau have to pass as parameter in sql server


--eg:

create procedure sample_sp
(
@.Enter_Operator_Name as varchar(100)
)
as

select * from tbl_Stats where operator = @.Enter_Operator_Name|||For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.
Dunno much about Oracle - but this sounds rather like Access which also is an integrated UI & dbms.

The gist with SQL Server is that the app will have to get the parameter value itself and then pass this to the sproc (a la mallier's answer).|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?

T-SQL is pretty different from Oracle with respect to handling variables. In T-SQL, you are going to end up having to do something like:

DECLARE @.MyVar int

SELECT @.MyVar = 100

SELECT * FROM tblCustomers WHERE CustID = @.MyVar

At runtime, you will have to replace the '= 100' with a value that you supply. Using SQLPlus, I know that you can do things from the command line calling files and replacing variables on the fly with parameters that you supply from the command line. This is doable in SQL Server as well, but you will need to use osql instead of the Query Analyzer to achieve the same effect (and even then I don't know exactly how to do it since I am not an osql guru).

Regards,

hmscott

Edit to add: You said "Procedure". Is this a stored proc you are trying to create? If so, in your stored proce declaration you can do this:

CREATE PROC spGetCustomer (@.CustID int)

AS

SET NOCOUNT ON

SELECT * FROM tblCustomer WHERE CustID = @.CustID|||SQL*Plus is an environment tool as well as a script execution tool. You can compare it to Microsoft Access in that respect, in that both allow easy integration between the UI and the database engine.

A pure database scripting tool can't (and shouldn't) support user interaction. It needs to execute scripts, and only execute scripts. If the data isn't coded into the script, you really need a different tool (such as VBS or Microsoft Access) to properly implement that functionality.

-PatP