Taking parsed string and assigning variables without using a temp table

Question

Post reply

Taking parsed string and assigning variables without using a temp table

demicoq

Old Hand

Points: 329
More actions
April 24, 2004 at 4:43 pm

#179617

Is there a quick way to take in a string of data that has been parsed and assign the parsed strings to variables, without first using a temp table? For example:

'Doe*Jane*12231968'

<Some parsing logic>

Desired parameter assignments:

@Fname,@Lname,@DOB

DemicoQ

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Actually, there is! It comes in the form of the PARSENAME command which was specifically developed to break apart the parts SQL object names "at the dots". Since your example string uses "*" as the "field seperator", all we have to do is REPLACE the "*" with "." and then use PARSENAME. An example follows:

DECLARE @SomeString VARCHAR(100) SET @SomeString = 'Doe*Jane*12231968'
SELECT PARSENAME(REPLACE(@SomeString,'*','.'),4) AS PART4, PARSENAME(REPLACE(@SomeString,'*','.'),3) AS PART3, PARSENAME(REPLACE(@SomeString,'*','.'),2) AS PART2, PARSENAME(REPLACE(@SomeString,'*','.'),1) AS PART1
Part4 Part3 Part2 Part1 ----- ----- ----- -------- NULL Doe Jane 12231968

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Frank Kalis

SSC Guru

Points: 111183

April 26, 2004 at 1:58 am

#504189

http://www.sommarskog.se/arrays-in-sql.html might give some ideas.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

vadba SSChampion Points: 11133 More actions · Answer 3

I use the following procedure, uspGetToken, to return the first substring. The original source string is modified by stripping the first token and the first delimiter string (which can be of any length).

The modified source string and the token are returned as OUTPUT parameters.

EXEC this stored procedure for each token (substring) that you expect.

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

DROP PROCEDURE uspGetToken

GO

CREATE PROCEDURE uspGetToken

(

@parm varchar(1000) OUTPUT,

@delim varchar(100),

@token varchar(1000) OUTPUT

)

AS

SET NOCOUNT ON

DECLARE @spos int

SET @spos = CHARINDEX( @delim , @parm, 1 )

IF @spos = 0

BEGIN

SET @token = @parm

SET @parm = ''

END

ELSE

BEGIN

SET @token = SubString( @parm, 1, @spos - 1)

SET @parm = Substring( @parm, @spos + Len(@delim), Len(@parm) - @spos )

END

GO

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

-- SAMPLE USAGE

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

DECLARE @S varchar(100), @d varchar(10)

DECLARE @Fname varchar(20), @Lname varchar(20), @DOB varchar(8)

SET @S = 'Doe*Jane*12231968'

SET @d = '*'

PRINT @S

SET NOCOUNT ON

EXEC uspGetToken @S OUTPUT, @d, @Fname OUTPUT

EXEC uspGetToken @S OUTPUT, @d, @Lname OUTPUT

EXEC uspGetToken @S OUTPUT, @d, @DOB OUTPUT

PRINT @Fname

PRINT @Lname

PRINT @DOB

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

Mike

demicoq Old Hand Points: 329 More actions · Answer 4

demicoq

Old Hand

Points: 329

May 1, 2004 at 11:29 pm

#504936

Thank you for responding. I really like the method you suggested...I'll give it a shot.

Demicoq