Solved

split string so it can be used for a where in statement

Posted on 2010-08-24
7
346 Views
Last Modified: 2012-05-10
I'd like to search for a number within a string, however the field in the table is a number
eg

MYSTRING IS PASSED AS A STRING TO THE STORED PROCEDURE
   select*
   from tbWholesalerProduct
     inner join tbProduct on (tbProduct.iProductId = tbWholesalerProduct.iProductId)
     inner join tbProductGroup on (tbProductGroup.iProductGroupId = tbProduct.iProductGroupId)
     inner join dbo.tbWholesalerProductPrice ON dbo.tbWholesalerProduct.iWholesalerProductId = dbo.tbWholesalerProductPrice.iWholesalerproductid
     where    (dbo.tbWholesalerProductPrice.vState like @vState)
      and    (tbWholesalerProduct.iWholesalerId = @iWholesalerId)
         and (tbProduct.iProductGroupId = @iProductGroupId) and (tbProduct.cStatus = 'A')
and  (tbProduct.iProductid in MYSTRING) -- iProductid  is an int column

Thanks
0
Comment
Question by:lgreally
[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
  • 2
  • 2
  • +1
7 Comments
 
LVL 2

Expert Comment

by:rogerperkins
ID: 33517215
Try this:

  select*
   from tbWholesalerProduct
     inner join tbProduct on (tbProduct.iProductId = tbWholesalerProduct.iProductId)
     inner join tbProductGroup on (tbProductGroup.iProductGroupId = tbProduct.iProductGroupId)
     inner join dbo.tbWholesalerProductPrice ON dbo.tbWholesalerProduct.iWholesalerProductId = dbo.tbWholesalerProductPrice.iWholesalerproductid
     where    (dbo.tbWholesalerProductPrice.vState like @vState)
      and    (tbWholesalerProduct.iWholesalerId = @iWholesalerId)
         and (tbProduct.iProductGroupId = @iProductGroupId) and (tbProduct.cStatus = 'A')
and  (tbProduct.iProductid = cast(MYSTRING as int))
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33517241
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33517244
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:lgreally
ID: 33517255
Hi Roger,

MYSTRING will be equal to something like
(1,12,4)

so it would need to work as an IN statement
where (tbProduct.iProductid in MYSTRING)
0
 
LVL 2

Expert Comment

by:rogerperkins
ID: 33517415
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 75 total points
ID: 33517591
You can greate a function to convert the list into a table of integers then use the function in your query
Something like

CREATE FUNCTION [dbo].[GET_INT_TABLE]
(
      @STRINPUT VARCHAR(MAX)      
)
RETURNS @INTTABLE TABLE
(
      VALUE INT
)
AS
BEGIN
      DECLARE @insertvalue varchar(32)
      DECLARE @pos INT
      DECLARE @nextpos INT
      DECLARE @valuelen INT

      SELECT @pos = 0, @nextpos = 1
      WHILE @nextpos > 0
      BEGIN
            SELECT @nextpos = charindex(',', @STRINPUT, @pos + 1)
            SELECT @valuelen = CASE
                              WHEN @nextpos > 0 THEN
                                    @nextpos
                                          ELSE
                                    len(@STRINPUT) + 1
                                   END - @pos - 1
         
        set @insertvalue = (substring(@STRINPUT, @pos + 1, @valuelen))                      
        if IsNumeric(@insertvalue) = 1
        begin
                  INSERT @INTTABLE (VALUE)
                  VALUES (@insertvalue)
            end
            SELECT @pos = @nextpos
      END
            
      RETURN       
END

Then in your stored procedure query
....
where (tbProduct.iProductid in (select value from GET_INT_TABLE(@MYSTRING)))
0
 

Author Closing Comment

by:lgreally
ID: 33518111
Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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