Parameter range of values

[CrystalDuck]

I've found numerous posts on the site that has very similar questions to mine...but nothing quite "on the money". So I thought I would post the code I am using and see what I'm doing wrong.

All I get back is a dataset for the #Dealer_Number_Search that looks like this:
Dealer_Number
------------------
1 0000015
2
3
4
5
6


Here's the code that make this happen. What step am I missing?

CREATE TABLE #DEALER_Number_Search (
Dealer_Number		char(7))

Declare @Dealer_number VARCHAR(100),
@len		int,
@start		int

SET @Dealer_Number='0000015,0000019,0000022,0055707,0055714,0055715'

--parse the Dealer Numbers
IF @Dealer_Number IS NOT NULL
BEGIN
	SET @start = 1
	SELECT @len = LEN(LTRIM(RTRIM(@Dealer_Number)))
	WHILE @start <= @len
    BEGIN
	SELECT @Dealer_Number = SUBSTRING(@Dealer_Number, @start,7)
        INSERT #DEALER_Number_Search VALUES (@Dealer_Number)
	SELECT @start = @start + 8
    END
END
	
Select * from #DEALER_Number_Search

Also...
is there a way to do this when you don't know the length of the parameter value? For example...

@Dealer_No= '0000015,000019,022,0055707,5714,00557'

Thank you for any help you can provide.


Thank you,
CrystalDuck

 

[SQLBill]

Here's a suggestion, I'll let you come up with the code.

Use CHARINDEX. You can use it to find where the comma is and then use that value in the SUBSTRING.

Refer to the BOL for information on CHARINDEX (look up PATINDEX while you are there).

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Posting advice: FAQ481-4875

 

[amrita418]

Your code looks good but you need to replace the @start= @start + 8 with

set @start = charindex(',', @Dealer_Number) + 1

Regards,
AA

 

[Catadmin]

In addition to what SQLBill suggested, I would suggestion declaring a counter variable, setting it to the # of commas counted in the string, and then using a While loop to parse through the Dealer Numbers.

An IF statement will only parse once, so you'll only ever get the first number (or whatever number your substring finds). A While statement, decremented properly, should get you all the dealer numbers regardless of how long the string is.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[CrystalDuck]

Okay...I researched a little further and found a perfect solution. I got it out of one of the FAQ threads...but now I don't remember which one. Sorry. *sheepish grin*

But here is the code I used. I had to create a function (dbo.Split) then use that to parse out my comma separated values. For anyone who needs it...here is what I did, that works like a charm!

CREATE Function dbo.Split(
   @InputText Varchar(4000), -- The text to be split into rows
   @Delimiter Varchar(10)) -- The delimiter that separates tokens.
                           -- Can be multiple characters, or empty

RETURNS @Array TABLE (
   TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if
                                          -- you don't want the
                                          -- identity column
   Value Varchar(4000))

AS

-----------------------------------------------------------
-- Function Split                                        --
--    • Returns a Varchar rowset from a delimited string --
-----------------------------------------------------------

BEGIN

   DECLARE
      @Pos Int,        -- Start of token or character
      @End Int,        -- End of token
      @TextLength Int, -- Length of input text
      @DelimLength Int -- Length of delimiter

-- Len ignores trailing spaces, thus the use of DataLength.
-- Note: if you switch to NVarchar input and output, you'll need to divide by 2.
   SET @TextLength = DataLength(@InputText)

-- Exit function if no text is passed in
   IF @TextLength = 0 RETURN

   SET @Pos = 1
   SET @DelimLength = DataLength(@Delimiter)

   IF @DelimLength = 0 BEGIN -- Each character in its own row
      WHILE @Pos <= @TextLength BEGIN
         INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1))
         SET @Pos = @Pos + 1
      END
   END
   ELSE BEGIN
      -- Tack on delimiter to 'see' the last token
      SET @InputText = @InputText + @Delimiter
      -- Find the end character of the first token
      SET @End = CharIndex(@Delimiter, @InputText)
      WHILE @End > 0 BEGIN
         -- End > 0, a delimiter was found: there is a(nother) token
         INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))
         -- Set next search to start after the previous token
         SET @Pos = @End + @DelimLength
         -- Find the end character of the next token
         SET @End = CharIndex(@Delimiter, @InputText, @Pos)
      END
   END
   
   RETURN

END

GO

Like I said I got this off this site from an FAQ thread...so I left it pretty well untouched. It was perfect!

This following code was for the actual parsed variables I needed to use. Utilizing the SPLIT function created above.

--Set BIN Value(s) (allows up to 3 BIN)
CREATE TABLE #BIN_Search (
BIN              char(28))

INSERT INTO #BIN_Search 
SELECT * FROM dbo.Split(@BIN,',')

--Set Account_Number Value(s)  (up to 10 account numbers)
CREATE TABLE #ACCT_No_Search (
Account_Number              char(100))

INSERT INTO #ACCT_No_Search 
SELECT * FROM dbo.Split(@Acct_No,',')

And to all of you above offering your help, I really appreciate it and I'm sorry it took me so long to get back to you to say so. This was a tricky bugger that took a little while. *haha*

Thanks again!
CrystalDuck