Solved

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

Posted on 2011-09-04
4
271 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to extract a number from a MS SQL server string. 39 70
How to query date ranges with SQL 6 37
Using OPENQUERY in a SELECT statement 6 34
Optimizing a query 3 33
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now