June 1, 2009 at 1:18 pm
Hello Guys..
I am using SQL 2005 and the following query does not return any data though actual data present in the column
select testcase_id from testcases(nolock) where scenarioname='[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]'
scenarioname is the column with varchar(max) data type.
Is there any limitation on wild chards within the string?
Appreciate quick help.
Jus
June 1, 2009 at 1:36 pm
I don't see any wild cards in your criteria. Plus wild cards don't work with "=".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 1, 2009 at 1:42 pm
Basically i was unable to undertsand why the condition
Scenarioname='[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]' is failing?
June 1, 2009 at 1:45 pm
How about the DDL (CREATE TABLE statement) for the table and some sample date (in a readily consummable format)? With that, we may be able to help you out better (Read the first article I reference below in my signature block).
June 1, 2009 at 1:49 pm
DDL:
CREATE TABLE [dbo].[testcases](
[testcase_id] [int] IDENTITY(1,1) NOT NULL,
[ScenarioName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[scenario_category] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[expected_bundles] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[expected_promotions] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_order] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_profile] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Change_to] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [datetime] NULL,
[user_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_crmm_testcase] PRIMARY KEY CLUSTERED
(
[testcase_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Data:
testcase_id scenarioname
1 [Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]
June 1, 2009 at 1:58 pm
Jus (6/1/2009)
DDL:CREATE TABLE [dbo].[testcases](
[testcase_id] [int] IDENTITY(1,1) NOT NULL,
[ScenarioName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[scenario_category] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[expected_bundles] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[expected_promotions] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_order] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_profile] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Change_to] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [datetime] NULL,
[user_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_crmm_testcase] PRIMARY KEY CLUSTERED
(
[testcase_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Data:
testcase_id scenarioname
1 [Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]
The DDL is okay, but there are two things wrong with your sample data. One, one record does not a sample make. Two, I really don't want to take the time edit the "sample" to get it into your table to test. It simply is not in a readily consummable format. Again, you may want to read the first article I reference below in my signature block if you would like better answers to your questions.
Edit: And actually, the "sample" data doesn't even match the DDL of the table.
You want help, you have to put in some effort to get the best responses.
June 1, 2009 at 2:03 pm
I just tested this:
set nocount on;
if object_id(N'tempdb..#testcases') is not null
drop table #testcases;
CREATE TABLE #testcases(
[testcase_id] [int] IDENTITY(1,1) NOT NULL,
[ScenarioName] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[scenario_category] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[expected_bundles] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[expected_promotions] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_order] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[on_profile] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Change_to] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [datetime] NULL,
[user_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_crmm_testcase] PRIMARY KEY CLUSTERED
(
[testcase_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
insert into #testcases ([ScenarioName])
select '[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]';
select *
from #testcases;
select *
from #testcases
where [ScenarioName] = '[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]';
Both select statements returned the correct results.
What do you get when you run that script?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2009 at 2:11 pm
I am sorry for not providing data lynn.
Data is a follows.
INSERT INTO testcases ([testcase_id],[ScenarioName],[scenario_category],[expected_bundles],[expected_promotions],[on_order],[on_profile],[Change_to],[creation_date],[user_name])
VALUES ('2737','[Change5]-[Existing Customer]-','Change','','AutoAdd: 1.24 M PL ','','FE / FDV + Data + Video Triple','FE / FDV + Data ','','')
INSERT INTO testcases ([testcase_id],[ScenarioName],[scenario_category],[expected_bundles],[expected_promotions],[on_order],[on_profile],[Change_to],[creation_date],[user_name])
VALUES ('2738','[Change6]-[Existing Customer]-','Change','','AutoAdd: 1.24 M PL ','','FE / FDV + Video SPL FL DCT wi','FE / FDV + Data ','','')
INSERT INTO testcases ([testcase_id],[ScenarioName],[scenario_category],[expected_bundles],[expected_promotions],[on_order],[on_profile],[Change_to],[creation_date],[user_name])
VALUES ('2739','[Change7]-[Existing Customer]-','Change','','AutoAdd: 1.24 M PL ','','FE / FDV + Data +Video SPL FL ','FE / FDV + Data ','','')
Regards
Jus
June 1, 2009 at 2:12 pm
Jus (6/1/2009)
Basically i was unable to undertsand why the conditionScenarioname='[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]' is failing?
But the question in your original post which I answered is:
Is there any limitation on wild chards within the string?
Your question now is different. Not to sound condescending, but if you are not getting any rows for that criteria it means that the criteria is not being matched.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 1, 2009 at 2:13 pm
If you notice, GSquared took the time to format your code the way you should when posting questions.
June 1, 2009 at 2:15 pm
Hello GSquared..
Wonder is that when i ran your code it works...but in my db, it returns no records...
--Jus
June 1, 2009 at 2:20 pm
Okay, now tell us what you are trying accomplish. I can't see a correlation between your data and the query from your original post. Possible that I am slightly blind as I am a soccer referee, and been accussed of being blind before.
June 1, 2009 at 2:28 pm
Lynn Pettis (6/1/2009)
Okay, now tell us what you are trying accomplish. I can't see a correlation between your data and the query from your original post. Possible that I am slightly blind as I am a soccer referee, and been accussed of being blind before.
Yeah but with those ears you can pick up just about anything via sound. :w00t:
To answer the OP's original question, do you get any results back from this code:
Select * From testcases where Scenarionam Like '%[Change6]-[Existing Customer]%'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 1, 2009 at 2:38 pm
Jack Corbett (6/1/2009)
Lynn Pettis (6/1/2009)
Okay, now tell us what you are trying accomplish. I can't see a correlation between your data and the query from your original post. Possible that I am slightly blind as I am a soccer referee, and been accussed of being blind before.Yeah but with those ears you can pick up just about anything via sound. :w00t:
To answer the OP's original question, do you get any results back from this code:
Select * From testcases where Scenarionam Like '%[Change6]-[Existing Customer]%'
Original question is about equals, not "Like". If you want to use "Like", you'll have to escape some of the characters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2009 at 2:38 pm
Jus (6/1/2009)
Hello GSquared..Wonder is that when i ran your code it works...but in my db, it returns no records...
--Jus
That almost certainly means there are no exact matches for that Where clause then. Might be something close, but nothing that's an exact match.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply