looking for a query to split string and save each portion to variables

Question

Post reply

looking for a query to split string and save each portion to variables

halifaxdal

SSCoach

Points: 19741
More actions
December 15, 2016 at 11:16 am

#401215

I have a db with a field like 'US01/00000/12345/123456', I need to split it into 4 parts and save each part to a variable, there are existing solution to split it into a table but I need the result differently
Input:
@STR = 'US01/00000/12345/123456'
Output:
@sub1 = 'US01'
@sub2 = '00000'
@sub3 = '12345'
@sub4 = '123456'
Can anyone help? Thank you very much.
I am not on 2016, so the split_string function cannot be used in this case.

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

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

halifaxdal SSCoach Points: 19741 More actions · Answer 1

Sorry, for the later visitor, here is the solution:

DECLARE @test-2 varchar(max);

set @test-2 = 'US01/00000/12345/123456';

set @test-2 = Replace(@test, '/', '.');

SELECT ParseName(@test, 4)

SELECT ParseName(@test, 3)

SELECT ParseName(@test, 2)

SELECT ParseName(@test, 1)

sgmunson SSC Guru Points: 110640 More actions · Answer 2

halifaxdal (12/15/2016)
Sorry, for the later visitor, here is the solution:
DECLARE @test-2 varchar(max);
set @test-2 = 'US01/00000/12345/123456';
set @test-2 = Replace(@test, '/', '.');
SELECT ParseName(@test, 4)
SELECT ParseName(@test, 3)
SELECT ParseName(@test, 2)
SELECT ParseName(@test, 1)

Okay, but if your only output is the variables themselves, then you probably don't need T-SQL for this. What will those variables be doing? If they're going to be used in other queries, then you can still use the DelimitedSplit8K function described here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

Here's a skeleton idea for it:

SELECT *

FROM YourTable AS YT

CROSS APPLY dbo.DelimitedSplit8K(@test, '/') AS S

WHERE S.ItemNumber = -- Put the element number within @test-2 here

AND S.Item = YT.SomeField

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

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Thank you Steve,

Your solution is nice however will sometimes not applicable to some cases, like mine: I am not allowed to create stored procedures or functions on that server. 🙂

sgmunson SSC Guru Points: 110640 More actions · Answer 4

halifaxdal (12/15/2016)
Thank you Steve,
Your solution is nice however will sometimes not applicable to some cases, like mine: I am not allowed to create stored procedures or functions on that server. 🙂

Then take the code from the function and place the appropriate pieces inline into your query. You don't have to have it as a function. So yes, it's still applicable. Just not in the way you thought... 🙂

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

halifaxdal (12/15/2016)
Thank you Steve,
Your solution is nice however will sometimes not applicable to some cases, like mine: I am not allowed to create stored procedures or functions on that server. 🙂

Did you ask if the already had the function or something like it?

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