Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search does not work on varchar(max)


Search does not work on varchar(max)

Author
Message
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 275
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 14858
I don't see any wild cards in your criteria. Plus wild cards don't work with "=".



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 275
Basically i was unable to undertsand why the condition
Scenarioname='[Change6]-[Existing Customer]-[FE / FDV + Video SPL FL DCT with VTP = NO]' is failing?
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24257 Visits: 37978
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).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 275
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]
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24257 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 275
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11040 Visits: 14858
Jus (6/1/2009)
Basically i was unable to undertsand why the condition
Scenarioname='[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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24257 Visits: 37978
If you notice, GSquared took the time to format your code the way you should when posting questions.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search