Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft SQL Server
Reload this Page

Searching for a single quote

Discuss Searching for a single quote in the Microsoft SQL Server forum on ASP Free.
Searching for a single quote Microsoft SQL Server forum discussing administration and other SQL Server related topics. MS SQL Server is used for high performance and enterprise level applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old December 16th, 2004, 04:15 PM
JeffA JeffA is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 12 JeffA User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 14 m 42 sec
Reputation Power: 0
Cool Searching for a single quote
I am having trouble searching for a single quote in a column
I have been trying to use
SELECT * FROM TABLE WHERE COLUMN LIKE '%'%'
THE ESCAPE COMMAND DOES NOT SEEM TO WORK EITHER
Reply With Quote
  #2  
Old December 17th, 2004, 05:10 PM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,783 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095
Are you trying to select the column names? I could of course be wrong on this, but I think the select will only give you the data IN the table, not the column names.

I was thinking of a way you might be able to do this. I have never done it, but....
First determine the ID from sysobjects that corresponds to your table, then:
Code:
 
SELECT
	syscolumns.name,
	syscolumns.id
 
FROM
	sysobjects
 
JOIN
	syscolumns ON
	sysobjects.id = syscolumns.id
 
WHERE
	sysobjects.id = 123456 --123456 relates to the ID of your table.


As for the single quote, it will not search for that character. You will have to use some other character. I wish I could be more helpful! Also, I would think that you would not be able to use the single quote in a column name anyway, but I have not tried it.

You could, of course, modify the above code to return all the column names of whichever table(s) you are evaluating.
Reply With Quote
  #3  
Old December 19th, 2004, 05:27 PM
JeffA JeffA is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 12 JeffA User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 14 m 42 sec
Reputation Power: 0
Cool Apologies
Sorry,
I didn't explain myself very well
I was refering to the data within the column not the column
JeffA
Reply With Quote
  #4  
Old December 19th, 2004, 06:13 PM
Doug G Doug G is offline
Grumpier Old Moderator
ASP Free God 11th Plane (10000 - 10499 posts)
  
Join Date: Sep 2003
Posts: 10,143 Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 22 h 23 m 39 sec
Reputation Power: 181
Try doubling up the ' in your sql LIKE. MS databases 'escape' the ' character by doubling it. in the sql.
__________________
======
Doug G
======
I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
Reply With Quote
  #5  
Old December 19th, 2004, 07:54 PM
JeffA JeffA is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 12 JeffA User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 14 m 42 sec
Reputation Power: 0
Smile Thanks Doug G
Amazing how simple it is

JeffA
Reply With Quote
  #6  
Old December 20th, 2004, 10:47 AM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,783 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095
Quote:
Originally Posted by Doug G
Try doubling up the ' in your sql LIKE. MS databases 'escape' the ' character by doubling it. in the sql.

LOL you know I thought of that. But the only way I could get that to work in my testing was to have four single quotes, since two right next to each other indicate a zero length string. Is that a workable solution in SQL?
Reply With Quote
  #7  
Old December 20th, 2004, 10:59 AM
Doug G Doug G is offline
Grumpier Old Moderator
ASP Free God 11th Plane (10000 - 10499 posts)
  
Join Date: Sep 2003
Posts: 10,143 Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 22 h 23 m 39 sec
Reputation Power: 181
Code:
SELECT * FROM TABLE WHERE COLUMN LIKE '%''%'

This shouldn't interpret the inner ' as a zero length string, since the doubled ' are inside a string variable.
Reply With Quote
  #8  
Old December 20th, 2004, 11:24 AM
Lauramc's Avatar
Lauramc Lauramc is offline
SQL Slarentice
ASP Free Intermediate (1500 - 1999 posts)
  
Join Date: Nov 2004
Location: In My Happy Place
Posts: 1,783 Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level)Lauramc User rank is General 2nd Grade (Above 100000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 15 h 48 m 56 sec
Reputation Power: 1095
Nice tip Thanks!!!!
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft SQL Server > Searching for a single quote

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway. 
Stay green...Green IT