Split without delimiter based on position

Question

Post reply

Split without delimiter based on position

Viewing 15 posts - 16 through 30 (of 35 total)

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

yuvipoy SSChampion Points: 10469 More actions · Answer 1

create procedure @STR varchar(1000),@param int

as

begin

.

i am having values in splitter table so there is no need of @pos here we can directly take the value from splitter table.

Splitter table

Rid , Delimiter

1,1

2,1

3,2

4,5

5,4

6,2

.

end

exec @STR ='12345678910111213141516171819202122232425',@param=6

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

yuvipoy (10/22/2012)
create procedure @STR varchar(1000),@param int
as
begin
.
.
i am having values in splitter table so there is no need of @pos here we can directly take the value from splitter table.
Splitter table
Rid , Delimiter
1,1
2,1
3,2
4,5
5,4
6,2
.
.
.
.
end
exec @STR ='12345678910111213141516171819202122232425',@param=6

The string splitting would ideally be in the stored procedure, so you would have parameters @STR and @pos for the stored procedure.

Where do @STR and @pos come from?

What will you do with the output of the stored procedure? This bit:

RidCol1Col2Col3Col4Col5Col6

1123456789101112

[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]

yuvipoy SSChampion Points: 10469 More actions · Answer 3

The string splitting would ideally be in the stored procedure, so you would have parameters @STR and @pos for the stored procedure.
Where do @STR and @pos come from?
What will you do with the output of the stored procedure? This bit:
RidCol1Col2Col3Col4Col5Col6
1123456789101112

1) the input is from .net code c# file

2) need to insert into a table

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

yuvipoy (10/22/2012)
The string splitting would ideally be in the stored procedure, so you would have parameters @STR and @pos for the stored procedure.
Where do @STR and @pos come from?
What will you do with the output of the stored procedure? This bit:
RidCol1Col2Col3Col4Col5Col6
1123456789101112
1) the input is from .net code c# file
2) need to insert into a table

Ok, after you have done 2) insert into a table, what uses the table? A report, perhaps?

[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]

yuvipoy SSChampion Points: 10469 More actions · Answer 5

Ok, after you have done 2) insert into a table, what uses the table? A report, perhaps?

NO This is not a report one i need to join some tables and do some calculations and some business logic's are there.

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

yuvipoy (10/22/2012)
Ok, after you have done 2) insert into a table, what uses the table? A report, perhaps?
NO This is not a report one i need to join some tables and do some calculations and some business logic's are there.

Ok, thanks. Can you give an example of how the data from this output table would be used please?

[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]

yuvipoy SSChampion Points: 10469 More actions · Answer 7

Ok, thanks. Can you give an example of how the data from this output table would be used please?

ChrisM as i have said i am having some business logic's on it i can't reveal on forums.

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

yuvipoy (10/22/2012)
Ok, thanks. Can you give an example of how the data from this output table would be used please?
ChrisM as i have said i am having some business logic's on it i can't reveal on forums.

No problem, I'm fully qualified to work with really secret stuff.

Can you mock up a query? It's quite important that you understand the implications of using a table with an unknown number of columns.

[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]

yuvipoy SSChampion Points: 10469 More actions · Answer 9

Can you mock up a query? It's quite important that you understand the implications of using a table with an unknown number of columns.

Column is fixed but i may not get full columns from the input string,i may get 6 columns or 10 or 15 or 23 or 11 and so on the table column is fixed rest of the fields are set as null say my table will contain 40 columns.

Thanks!

CapnHector SSCoach Points: 16143 More actions · Answer 10

ok this gets you almost what i think you want. its something i did as i need to work on rCTE's and pivot tables and with any sort of data volumes this will slow to a crawl.

it puts out in the denormalized format you are requesting and uses DelimitedSplit8k as the other examples.

declare @SomeString varchar(50) = '12345678910111213141516'

declare @Pos varchar(50) = '1,1,2,5,4,2'

;WITH split AS (SELECT 1 as Rid, ItemNumber AS id, cast(Item AS INT) as Item FROM udf_DelimitedSplit8K(@Pos,',')),

start AS (SELECT rid, id AS id, 1 as start, Item as length

FROM split

WHERE id = 1

UNION ALL

SELECT st.rid, s.id, start + st.length, s.Item

FROM split s

INNER JOIN start st

ON st.id + 1 = s.id)

SELECT Rid, [1],[2],[3],[4],[5],[6]

FROM (

SELECT Rid, id, SUBSTRING(@SomeString,Start,length) as item FROM start

) p

PIVOT

(

MAX(item)

FOR id IN ([1],[2],[3],[4],[5],[6])

) AS pvt

if you need a dynamic pivot table it can get fun and i would suggest you look to use a more normalized data structure if you can make that change.

EDIT found an error in my query. i corrected above

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

yuvipoy SSChampion Points: 10469 More actions · Answer 11

I have already created dynamic Pivot table in my 3rd post

http://www.sqlservercentral.com/Forums/Topic1374200-391-1.aspx

but the thing is it is consuming more time so i am looking for a alternate method.

Thanks!

yuvipoy SSChampion Points: 10469 More actions · Answer 12

yuvipoy

SSChampion

Points: 10469

October 23, 2012 at 3:22 am

#1551797

Any suggestions how can we optimize the query.

Sean Lange SSC Guru Points: 286573 More actions · Answer 13

yuvipoy (10/23/2012)
Any suggestions how can we optimize the query.

Can you do the pivot in the front end? That would release the pressure from sql and put it in a system that can handle pivots a lot easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

yuvipoy SSChampion Points: 10469 More actions · Answer 14

I am not doing any report kind of stuff , after getting the string i need to split those string then i need to insert to corresponding columns and do some internal calculations (say update, delete),so there is no point in time to have a help from front end.

Thanks!

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 15

Part of the problem is that what you're calling "position" and "delimiter" is actually the length of the desired field and that confused even me when I first looked at it.

The other thing that isn't helping is you keep posting text instead of readily consumable information such as what you posted for your "splitter table". Please see the first link in my signature line below. You'll get much better help if you post readily consumable data like that for things like your splitter table in the future.

I've created a "splitter table" for you and called it more for what it really is... a RowLayout table. Here's the code for that. Notice that since this a static table, I took the time to calculate the actual starting positions by hand and then hard-coded them into the table. You should, too. No sense in recalulating that which does not change.

CREATE TABLE dbo.RowLayout

(

RowLayout# SMALLINT,

Column# SMALLINT,

ColumnStart INT,

ColumnLength SMALLINT,

CONSTRAINT PK_RowLayout PRIMARY KEY CLUSTERED (RowLayout#,Column#)

)

;

INSERT INTO dbo.RowLayout

(RowLayout#, Column#, ColumnStart, ColumnLength)

SELECT 1,1, 1,1 UNION ALL

SELECT 1,2, 2,1 UNION ALL

SELECT 1,3, 3,2 UNION ALL

SELECT 1,4, 5,5 UNION ALL

SELECT 1,5,10,4 UNION ALL

SELECT 1,6,14,2 UNION ALL

SELECT 2,1, 1,1 UNION ALL

SELECT 2,2, 2,1 UNION ALL

SELECT 2,3, 3,2 UNION ALL

SELECT 2,4, 4,5 UNION ALL

SELECT 3,1, 1,1 UNION ALL

SELECT 3,2, 2,1 UNION ALL

SELECT 3,3, 3,2 UNION ALL

SELECT 3,4, 5,5 UNION ALL

SELECT 3,5,10,4 UNION ALL

SELECT 3,6,14,2 UNION ALL

SELECT 3,7,16,2

Up next, the stored procedure that you wanted. I really question this RBAR method you wanted. If you really need to split a whole table's worth of data, post back and we'll see what we can do. Remember to include some readily consumable test data like I told you at the beginning of this post.

I also used sp_executesql to help prevent SQL Injection attacks.

CREATE PROCEDURE dbo.SplitByRowLayout

@pStringToSplit VARCHAR(8000),

@pRowLayout# SMALLINT

AS

--===== We need a place to hold the Dynamic SQL

DECLARE @SQL NVARCHAR(MAX)

;

--===== Build the SELECT List Columns using the given row layout

SELECT @SQL = ISNULL(@SQL+N','+CHAR(10),'') +

+ N'Col' + RIGHT('0000'+CAST(Column# AS NVARCHAR(10)),4) + N'='

+ N'SUBSTRING(@pStringToSplit,'

+ CAST(ColumnStart AS NVARCHAR(10))+','

+ CAST(ColumnLength AS NVARCHAR(10))+')'

FROM dbo.RowLayout

WHERE RowLayout# = @pRowLayout#

ORDER BY Column#

;

--===== Prepend the Dynamic SQL with the word "SELECT"

SELECT @SQL = N'SELECT' + CHAR(10) + @SQL

;

EXEC sp_executesql @SQL, N'@pStringToSplit VARCHAR(8000)', @pStringToSplit = @pStringToSplit

;

The, you can just run the queries. Here's the same data split 3 different ways according to the RowLayout table.

EXEC dbo.SplitByRowLayout @pStringToSplit = '12345678910111213141516171819202122232425', @pRowLayout# = 1

EXEC dbo.SplitByRowLayout @pStringToSplit = '12345678910111213141516171819202122232425', @pRowLayout# = 2

EXEC dbo.SplitByRowLayout @pStringToSplit = '12345678910111213141516171819202122232425', @pRowLayout# = 3

The results from that are...

Col0001 Col0002 Col0003 Col0004 Col0005 Col0006

1 2 34 56789 1011 12

Col0001 Col0002 Col0003 Col0004

1 2 34 45678

Col0001 Col0002 Col0003 Col0004 Col0005 Col0006 Col0007

1 2 34 56789 1011 12 13

You can use either INSERT/EXEC or OPENQUERY to use the results of the stored procedure as if it were a table result set.

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