Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Split without delimiter based on position Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 2:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155

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
Post #1374200
Posted Thursday, October 18, 2012 3:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:35 AM
Points: 888, Visits: 672
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

Post #1374228
Posted Thursday, October 18, 2012 4:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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
Post #1374267
Posted Thursday, October 18, 2012 6:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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)
Post #1374293
Posted Friday, October 19, 2012 2:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
Without while loop can we do this?
since loop is taking more time any other alternate method

Thanks!
Post #1374683
Posted Friday, October 19, 2012 4:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1374712
Posted Friday, October 19, 2012 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1374869
Posted Friday, October 19, 2012 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1374893
Posted Monday, October 22, 2012 12:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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.

Post #1375258
Posted Monday, October 22, 2012 3:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 1,037, Visits: 7,027
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
Post #1375310
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse