Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-08-24
7
Medium Priority
?
348 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 300 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

721 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