Parse data in field???

Question

Parse data in field???

dcoffaro

Grasshopper

Points: 20
More actions
September 24, 2010 at 7:48 pm

#227073

I have a column with the following data:
MyService.1.34.5
YourService.34.1.34
HerService.3.8.9
I want to take the value starting from the right until the first period and update a column in the same table with the value excluding the period (for all values until the last period)
The output should resemble with the first line being the column names:
ServiceName Break Start Stop
MyService 1 34 5
YourService 34 1 34
HerService 3 8 9
There isn't a static number of characters between each period, this is what's giving me the headache...
Any help would great,
Cheers,
DC

Viewing 8 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

there is a built in function PARSENAME that can help do this, but it only will help if your data has three periods in it.

the function is usually used for splitting servername.database.schemaname.tablename, but works for IP addresses and other things with 4 parts/3 periods.

/*

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

MyService 1 34 5

YourService 34 1 34

HerService 3 8 9

*/

select

parsename(TheField,4),

parsename(TheField,3),

parsename(TheField,2),

parsename(TheField,1)

from

(select 'MyService.1.34.5' as TheField union all

select 'YourService.34.1.34' union all

select 'HerService.3.8.9'

) x

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

You could use a split string fuction (search for "DelimitedSplit8k" on this site for a T-SQL solution).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 3

Lowell (9/25/2010)
there is a built in function PARSENAME that can help do this, but it only will help if your data has three periods in it.
the function is usually used for splitting servername.database.schemaname.tablename, but works for IP addresses and other things with 4 parts/3 periods.
/*
-------------- ------ ----- ----
MyService 1 34 5
YourService 34 1 34
HerService 3 8 9
*/
select
parsename(TheField,4),
parsename(TheField,3),
parsename(TheField,2),
parsename(TheField,1)
from
(select 'MyService.1.34.5' as TheField union all
select 'YourService.34.1.34' union all
select 'HerService.3.8.9'
) x

I always tend to forget considering PARSENAME for such a scenario. Most probably the better approach!! (I never compared PARSENAME vs. any split string function (e.g. the tally table solution) but I'm sure the code MS came up with is tuned to deal with that specific setup).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323546 More actions · Answer 4

i always hope they'll enhance it a bit so we can use it for more than 4 parts; IP6 stuff is starting to be more common, and other uses as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 5

How's this?

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

declare @test TABLE (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

MyColumn varchar(50));

insert into @test

SELECT 'MyService.1.34.5' UNION ALL

SELECT 'YourService.34.1.34' UNION ALL

SELECT 'HerService.3.8.9';

-- latest version of the DelimitedSplit8K function is at

-- http://www.sqlservercentral.com/Forums/FindPost944589.aspx

SELECT t.*,

ServiceName = MAX(CASE WHEN ds.ItemNumber = 1 THEN Item ELSE NULL END),

[Break] = MAX(CASE WHEN ds.ItemNumber = 2 THEN Item ELSE NULL END),

Start = MAX(CASE WHEN ds.ItemNumber = 3 THEN Item ELSE NULL END),

[Stop] = MAX(CASE WHEN ds.ItemNumber = 4 THEN Item ELSE NULL END)

FROM @test t

CROSS APPLY dbo.DelimitedSplit8K(MyColumn, '.') ds

GROUP BY t.RowID, t.MyColumn;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

dcoffaro Grasshopper Points: 20 More actions · Answer 6

Thanks guys, I'll give them a shot today/tomorrow are post the results.

Cheers,

-DC

Steve Jones - SSC Editor SSC Guru Points: 738673 More actions · Answer 7

Test post for code. This is Wayne's code