Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Split Linestrings and work with the results

Author  Topic 

purplehaze35
Starting Member

1 Post

Posted - 2014-11-20 : 05:06:25
y question is how to build a function in PL/pgSQL to do this :

i have a db with many linestrings like this.

Linestring((3.584731 60.739211,3.590472 60.738030,3.592740 60.736220))

I need to split every Linestring in a Substring and split it up again in 2 coordinates like this.

3.584731 60.739211
x1 y1

3.590472 60.738030
x2 y2


3.592740 60.736220
x3 y3

And so on with the other points. Save the answers and converting into a double so I can calculate with the points.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 09:12:08
See: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page

notmyrealname

98 Posts

Posted - 2014-11-25 : 14:33:43
I have a numbers table in my database that I reference frequently. Mostly to provide row numbers. I like it more than the ROW_NUMBER function.


CREATE TABLE [dbo].[NumberList] (
[Number] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_NumberList] PRIMARY KEY CLUSTERED ([Number] ASC)
);


Anyway, if you create a NumberList table with just an integer column called Number and populate with say 1-10000 you can use the following query.


SELECT SUBSTRING([Values].Value, NumberList.Number, CHARINDEX(',', [Values].Value + ',', NumberList.Number) - NumberList.Number) AS SplitValue
FROM [Values] INNER JOIN
NumberList ON LEN([Values].Value) >= NumberList.Number
WHERE (SUBSTRING(',' + [Values].Value + ',', NumberList.Number, 1) = ',')


... where Values is a table that has a column called Value with all of your comma seperated values.

It's really fast. I don't know how it stacks up the tally table solution. I am still running SQL Server 2005. You might be able do this in 2008+ without the numbers table. Maybe even 2005.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2014-11-28 : 22:06:52
quote:
Originally posted by notmyrealname

I have a numbers table in my database that I reference frequently. Mostly to provide row numbers. I like it more than the ROW_NUMBER function.


CREATE TABLE [dbo].[NumberList] (
[Number] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_NumberList] PRIMARY KEY CLUSTERED ([Number] ASC)
);


Anyway, if you create a NumberList table with just an integer column called Number and populate with say 1-10000 you can use the following query.


SELECT SUBSTRING([Values].Value, NumberList.Number, CHARINDEX(',', [Values].Value + ',', NumberList.Number) - NumberList.Number) AS SplitValue
FROM [Values] INNER JOIN
NumberList ON LEN([Values].Value) >= NumberList.Number
WHERE (SUBSTRING(',' + [Values].Value + ',', NumberList.Number, 1) = ',')


... where Values is a table that has a column called Value with all of your comma seperated values.

It's really fast. I don't know how it stacks up the tally table solution. I am still running SQL Server 2005. You might be able do this in 2008+ without the numbers table. Maybe even 2005.



You absolutely have the right idea. If you have a look at the article that a link was provided for above, you'll see that the concatenation of commas (delimiters) makes that type of code pretty slow when you get to larger strings. You need to avoid the concatenation and the article shows you how to do that with or without the use of a physical numbers table.

--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."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."
Go to Top of Page
   

- Advertisement -