SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split without delimiter based on position


Split without delimiter based on position

Author
Message
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
I am having a query i need to split the query based on the position i specify
Say
string is 12345678910111213141516........
i am having position as 1,1,2,5,4,2
i need as
1
2
34
56789
1011
12

Thanks
subbu1
subbu1
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1295 Visits: 695
USE BELOW CODE...
--DROP TABLE #temp1
declare @str varchar(100)='12345678910111213141516'
declare @pos varchar(50)='1,1,2,5,4,2'
declare @t int=0
create table #temp1(id varchar(100))
WHILE CHARINDEX(',',@pos)>0
BEGIN
set @t=SUBSTRING(@pos,1,(CHARINDEX(',',@pos)-1))
INSERT INTO #temp1 VALUES( substring(@str,1,@t))
SET @pos=SUBSTRING(@pos,(CHARINDEX(',',@pos))+1,LEN(@pos))
set @str=SUBSTRING(@str,(@t+1),len(@str))
END
INSERT INTO #temp1 VALUES (SUBSTRING(@str,1,CONVERT(INT,@pos)))
--INSERT INTO @T1 VALUES(@VAL)
SELECT * FROM #temp1
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
hey it has worked thanks
now the thing is i need to insert this data into a table

my table is
create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)

i need to insert this data i this table like

insert into testtable values ........
and my result from this table will be like something

Select * from testtable

Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12

Thanks
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
I have used Pivot pls correct me if i have done wrong

DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + Convert(varchar,Rid )+ ']', '[' + Convert(varchar,Rid ) + ']')
FROM #Temp

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT '+
@cols +'FROM (SELECT [Rid],id FROM #Temp) p
PIVOT
(min([id]) FOR [Rid] IN ( '+@cols +' )) AS pvt'

print @query

EXECUTE(@query)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
Without while loop can we do this?
since loop is taking more time any other alternate method

Thanks!
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
Hi
I am getting the error as

declare @pi_input varchar(max)
declare @query varchar(max)
select @pi_input ='0123456'
SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'
print @query
exec (@query)

Select SUBSTRING(@Input,0,1)
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Input".

how to resolve it

thanks!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59378 Visits: 17947
yuvipoy (10/19/2012)
Hi
I am getting the error as

declare @pi_input varchar(max)
declare @query varchar(max)
select @pi_input ='0123456'
SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'
print @query
exec (@query)

Select SUBSTRING(@Input,0,1)
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Input".

how to resolve it

thanks!


Not sure what this has to do with your original question but your dynamic sql is nowhere close here. You have @Input in your dynamic sql. That variable MUST be declared inside the dynamic sql. That variable is not declared outside your dynamic sql either so I am not sure what that is. Honestly from you posted I don't see any reason for dynamic sql at all.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59378 Visits: 17947
This looked like an extremely odd situation but a fun challenge. It can in fact be accomplished without loops. You have to combine a couple of techniques, both the delimited split and the quirky update.

You need to read the article referenced in my signature about splitting strings. In there you will find the code for the DelimitedSplit8K function.

Then you need to read this article about running totals. http://www.sqlservercentral.com/articles/T-SQL/68467/

Why running totals? I use the running total to keep track of the length of the string at the time of each split.

Make sure that you read and understand the concepts in both articles. These are pretty advanced topics and you are the one who has to support this code.


declare @SomeString varchar(50) = '12345678910111213141516'

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

--For the quirky update we need this data in a table of some sort.
create table #StringOutput
(
RowNum int,
ColLength int,
SumOfLength int
)

insert #StringOutput
select *, null --The null will be updated with the running total
from dbo.DelimitedSplit8K(@pos, ',')

declare @Total int = 0

--this is the quirky update used to store the running total
update #StringOutput
set @Total = SumOfLength = @Total + ColLength
OPTION (MAXDOP 1)

--now that we know where in the original string and the length of each segment we just need to get the correct substring for each segment.
select *, substring(@SomeString, SumOfLength - ColLength + 1, ColLength) as FinalOutput
from #StringOutput

drop table #StringOutput



_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2254 Visits: 1387
Hi, thanks i have learned one new thing today,
but the thing is i need to insert into the testtable


Select * from testtable

Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12

my testtable will be a dynamic one based on the input of the position in my example i have given
declare @str varchar(100)='12345678910111213141516'
declare @pos varchar(50)='1,1,2,5,4,2'

@pos may increase or decrease if it increase i need to create my testtable accordingly ,here it is 6 splits so i am having 6 columns later i may get 15 columns so i need to create a testtable with 15 columns and so on and i need to insert the data vertically after splitting.
ChrisM@home
ChrisM@home
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4934 Visits: 10605
yuvipoy (10/18/2012)
hey it has worked thanks
now the thing is i need to insert this data into a table

my table is
create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)

i need to insert this data i this table like

insert into testtable values ........
and my result from this table will be like something

Select * from testtable

Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12

Thanks


The following query will meet this requirement:
DECLARE @str VARCHAR(100) = '12345678910111213141516'
DECLARE @pos VARCHAR(50) = '1,1,2,5,4,2'

;WITH
-- resolve @pos into a table
ResolvedString AS (
SELECT
ItemNumber,
Item = CAST(Item AS INT)
FROM dbo.DelimitedSplit8K(@pos,',')
)
-- crosstab query
SELECT
IDENTITY (int, 1, 1) AS Rid,
Col1 = MAX(CASE WHEN r.ItemNumber = 1 THEN y.Word ELSE NULL END),
Col2 = MAX(CASE WHEN r.ItemNumber = 2 THEN y.Word ELSE NULL END),
Col3 = MAX(CASE WHEN r.ItemNumber = 3 THEN y.Word ELSE NULL END),
Col4 = MAX(CASE WHEN r.ItemNumber = 4 THEN y.Word ELSE NULL END),
Col5 = MAX(CASE WHEN r.ItemNumber = 5 THEN y.Word ELSE NULL END),
Col6 = MAX(CASE WHEN r.ItemNumber = 6 THEN y.Word ELSE NULL END)
INTO #Temp
FROM ResolvedString r
-- use a triangular join to calculate the start position for SUBSTRING()
CROSS APPLY (
SELECT Startpos = 1+ISNULL(SUM(ir.Item),0)
FROM ResolvedString ir
WHERE ir.ItemNumber < r.ItemNumber
) x
CROSS APPLY (
SELECT Word = SUBSTRING(@str,x.Startpos,r.Item)
) y

SELECT * FROM #Temp



Couple of questions for you:
Where do the input string and the positions string come from? How are they created?
What's downstream from the new table?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search