Solved

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

Posted on 2010-08-24
7
345 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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:
ewangoya 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 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