Escaping characters in SQL SERVER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Escaping characters in SQL SERVER

Author

Topic

umapriya
Starting Member

5 Posts

Posted - 2007-03-29 : 12:29:13

Hi,

i have a field VPATH which holds virtual path of files from a .net project. i have created a search for a Library section.

i need to search only those pages which are inside "library" folder.

folder name: library

i tried to create a query using VPATH LIKE '%/library/%'. but it is showing result from all the folders. can anyone help me asap?

mail to :priya.interactive@yahoo.co.in.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-29 : 12:31:48

Some sample data and your whole query?

umapriya
Starting Member

5 Posts

Posted - 2007-03-29 : 13:40:47

data:
VPATH Content
/freegifts/f.aspx fff
/popup/b.ascx bbb
/library/l.ascx lll
/library/a.aspx aaa
/library/d.aspx ddd

the datatable contains VPATH as one of the fields in it.

i have two types of search in my site. one searches the entire site and other searches only library.

In the above table i need to filter the pages those are inside "library" folder only.

Query which i tried,

select Content from <tablename> where VPATH Like '%/library/%'

output i expected is:

Content
-------
lll
aaa
ddd

but i got all the records from the table..hope this example will be help ful for u understand what i need ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-29 : 13:45:42

What is the result that you got ?


declare @data	table
(
	VPATH	varchar(20),
	Content	varchar(30)
)
insert into @data
select	'/freegifts/f.aspx', 	'fff'	union all
select	'/popup/b.ascx', 	'bbb'	union all
select	'/library/l.ascx', 	'lll'	union all
select	'/library/a.aspx', 	'aaa'	union all
select	'/library/d.aspx', 	'ddd'

select	Content
from	@data
where	VPATH	like '%/library/%'
/*
Content                        
------------------------------ 
lll
aaa
ddd
*/

umapriya
Starting Member

5 Posts

Posted - 2007-03-29 : 14:26:50

I am sorry..actually my colleague is trying this..he could be able the get the above output in SQL Query Builder.

He is using a OLEDB namespace and objects on a ASP.NET web page. In this page he tried to build the query and execute the query which is not executing properly.

Can anyone help me?

Foldername: library

sample Table structure:
VPATH
------
/library/a.aspx
/library/b.aspx
/freegifts/c.aspx
/popup/d.aspx

Query: should return only those page's contents saved inside "library" folder.

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-29 : 15:00:23

Are you sure you mean escap char?

Do you wat to parse out the file name?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 15:56:56

[code]-- Prepare sample data
DECLARE @Sample TABLE (vPath VARCHAR(30))

INSERT @Sample
SELECT '/freegifts/f.aspx' UNION ALL
SELECT '/popup/b.ascx' UNION ALL
SELECT '/library/l.ascx' UNION ALL
SELECT '/library/a.aspx' UNION ALL
SELECT '/library/d.aspx'

-- Show the expected output
SELECT RIGHT(vpath, CHARINDEX('/', REVERSE(vPath)) - 1)
FROM @Sample
WHERE vPath LIKE '%/library/%'[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources