need help with parsing string

Question

need help with parsing string

is250sp

SSCarpal Tunnel

Points: 4361
More actions
January 8, 2010 at 3:46 pm

#136531

I have a script that finds the 2nd period and returns the first and second nodes in a string. It works fine as long as there is a second period, however, we've added more data without the second period and the script is no longer valid.
script:
select substring(@variable, 1, charindex('.', @variable) + 1 + charindex('.', substring(@variable, charindex('.', @variable) + 1, 100)) - 2
@variable values and desired return result:
Node1.Node2.Node3 would return Node1.Node2
Node1.Node2.Node3.Node4 would return Node1.Node2
Node1.Node2 would return Node1.Node2 (with the script above, this returns just Node1)

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks to me like you need a split function that works with a tally table. You shouldn't have to re-code your function every time the data chages.

You should be able to find several versions of a TSQL based split function here on SSC if you search. I'd recommend looking for one that used a numbers/tally table.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lowell SSC Guru Points: 323518 More actions · Answer 2

just use a case statement to check for the number of periods; if it's two or more, use your formula, else get the full string.

typical results:

Node1.Node2

ServerName.DatabaseName

192.168

Bob

code example:

select

CASE

WHEN (LEN(val) - len(replace(val,'.',''))) >= 2

THEN substring(val, 1, charindex('.', val) + 1 + charindex('.', substring(val, charindex('.', val) + 1, 100)) - 2)

ELSE VAL

END

from (

select 'Node1.Node2.Node3' as val union all

select 'Node1.Node2.Node3.Node4' union all

select 'Node1.Node2' union all

select 'ServerName.DatabaseName.SchemaName.objectName' union all

select '192.168.1.100' union all

select 'Bob' ) 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!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

is250sp (1/8/2010)
I have a script that finds the 2nd period and returns the first and second nodes in a string. It works fine as long as there is a second period, however, we've added more data without the second period and the script is no longer valid.
script:
select substring(@variable, 1, charindex('.', @variable) + 1 + charindex('.', substring(@variable, charindex('.', @variable) + 1, 100)) - 2
@variable values and desired return result:
Node1.Node2.Node3 would return Node1.Node2
Node1.Node2.Node3.Node4 would return Node1.Node2
Node1.Node2 would return Node1.Node2 (with the script above, this returns just Node1)

If the maximum number of nodes is 4, then you could use parsename()

SELECT PARSENAME (val, 1), PARSENAME (val, 2), PARSENAME (val, 3), PARSENAME (val, 4)

from (

select 'Node1.Node2.Node3' as val union all

select 'Node1.Node2.Node3.Node4' union all

select 'Node1.Node2' union all

select 'ServerName.DatabaseName.SchemaName.objectName' union all

select '192.168.1.100' union all

select 'Bob' ) x

Results:

(Col1)(Col2)(Col3) (Col4)

Node3Node2Node1NULL

Node4Node3Node2Node1

Node2Node1NULLNULL

objectNameSchemaNameDatabaseNameServerName

1001168192

BobNULLNULLNULL

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 4

A quick-and-dirty solution is to just add an extra period to each string during processing. Your script then runs without modification:

DECLARE @data TABLE (data VARCHAR(50) NOT NULL);

INSERT @data (data) VALUES ('N1.N2.N3.N4'), ('N1.N2.N3'), ('N1.N2'), ('N1');

WITH D (data) AS (SELECT data + '.' FROM @data)

SELECT SUBSTRING(data, 1, CHARINDEX('.', data) + 1 + CHARINDEX('.', SUBSTRING(data, CHARINDEX('.', data) + 1, 100)) - 2)

FROM D;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi