using varchar variable to query a table

Question

Post reply

using varchar variable to query a table

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Use a function to return the list as a table variable:

[font="Courier New"]CREATE FUNCTION [dbo].[uftSplitString]

(

@String VARCHAR(8000),

@Delimiter VARCHAR(255)

)

RETURNS

@Results TABLE

(

SeqNo INT IDENTITY(1, 1),

Item VARCHAR(8000)

)

AS

BEGIN

INSERT INTO @Results (Item)

SELECT SUBSTRING(@String+@Delimiter, number,

CHARINDEX(@Delimiter, @String+@Delimiter, number) - number)

FROM Numbers

WHERE number <= LEN(REPLACE(@String,' ','|'))

AND SUBSTRING(@Delimiter + @String,

number,

LEN(REPLACE(@Delimiter,' ','|'))) = @Delimiter

ORDER BY number RETURN

END

GO

-- Use the function:

DECLARE @ab VARCHAR (100)

SET @ab='483503,483504'

SELECT * FROM [dbo].[uftSplitString] (@ab, ',')

[/font]

Results:

SeqNo Item

----------- ------

1 483503

2 483504

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Chris,

You might want to make mention that your function relies on a "tally table" called "Numbers", as the OP here may or may not be familiar with that or with the technique using it to split strings.

Pramod,

Look up Jeff Moden's posts about "Tally table". It's a most-useful tool that is essential to Chris's user-defined function. You don't have to call the tally table "Numbers", but whatever you call it, be sure the function references it by the name you use.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

smunson (11/6/2008)
Chris,
You might want to make mention that your function relies on a "tally table" called "Numbers", as the OP here may or may not be familiar with that or with the technique using it to split strings.
Pramod,
Look up Jeff Moden's posts about "Tally table". It's a most-useful tool that is essential to Chris's user-defined function. You don't have to call the tally table "Numbers", but whatever you call it, be sure the function references it by the name you use.
Steve
(aka smunson)
:):):)

Hey Steve

Many thanks for the reminder! Good catch, mate!

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden