Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search does not work on varchar(max) Expand / Collapse
Author
Message
Posted Monday, June 1, 2009 1:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
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
Post #726931
Posted Monday, June 1, 2009 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 10,387, Visits: 13,454
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

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
Post #726940
Posted Monday, June 1, 2009 1:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
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?
Post #726945
Posted Monday, June 1, 2009 1:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 20,870, Visits: 32,913
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).



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)
Post #726949
Posted Monday, June 1, 2009 1:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
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]
Post #726952
Posted Monday, June 1, 2009 1:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 20,870, Visits: 32,913
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.



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)
Post #726955
Posted Monday, June 1, 2009 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #726960
Posted Monday, June 1, 2009 2:11 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
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
Post #726964
Posted Monday, June 1, 2009 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 10,387, Visits: 13,454
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

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
Post #726965
Posted Monday, June 1, 2009 2:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 20,870, Visits: 32,913
If you notice, GSquared took the time to format your code the way you should when posting questions.



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)
Post #726966
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse