Solved

how to trim first and last character of a parameter in sql

Posted on 2011-09-04
4
297 Views
Last Modified: 2012-05-12
i have the following sql

Declare @QuizID varchar(500)
set @QuizID='2,3,4,5,6'
butI need something like this
Select * tblQuiz where QuizID in (2,3,4,5,6)
how can i trim the apostrophe characcter in variable @QuizID
0
Comment
Question by:mmalik15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:mmalik15
ID: 36480174
this does not work
Select * tblQuiz where QuizID in ('2,3,4,5,6')
but this does work
Select * tblQuiz where QuizID in (2,3,4,5,6)
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36480260
 Declare @QuizID varchar(500)
set @QuizID=('2,3,4,5,6')


SELECT TOP 1000 [id]
      ,[mynum]
  FROM [dbPerm].[dbo].[ee_in_num_list] where  
   CHARINDEX( ',' + convert(varchar,mynum) + ',', ',' + @QuizID + ',' ) > 0
0
 
LVL 6

Expert Comment

by:yjchong514
ID: 36480265
Dear EE members,

Please refer the following site:
http://www.source-code.biz/snippets/mssql/1.htm

Good Luck!

Regards,

yjchong514
0
 
LVL 6

Accepted Solution

by:
c1nmo earned 500 total points
ID: 36480285
Updated to use your table and column names:-

Declare @QuizID varchar(500)
set @QuizID=('2,3,4,5,6')

SELECT *
  FROM tblQuiz  where  
   CHARINDEX( ',' + convert(varchar,QuizID) + ',', ',' + @QuizID + ',' ) > 0
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question