Error in max(substring)

Question

Post reply

Error in max(substring)

yuvipoy

SSChampion

Points: 10469
More actions
November 1, 2012 at 4:27 am

#393517

can we add
startid, endid SplitLayout
startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4
Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row 2+1=3 will be starting point for third row 3+1=4 will be starting point for fourth row 4+2=6 will be starting point for fifth row and so on ........
;with t1 as( select '1010' st ),
t2 as( Select startid, endid from SplitLayout),
t3 as ( select st,startid s,Endid e, row_number() over(order by startid) rn from t1,t2)
select * from t3
pivot
(
max(substring(st,s,e)) str for rn in ([1],[2],[3])
) AS pvt
i am getting error near substring( open bracket;
Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

ddl, sample data and desired output please. See the first link in my signature.

_______________________________________________________________

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

yuvipoy (11/1/2012)
can we add
startid, endid SplitLayout
startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4
Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row 2+1=3 will be starting point for third row 3+1=4 will be starting point for fourth row 4+2=6 will be starting point for fifth row and so on ........
;with t1 as( select '1010' st ),
t2 as( Select startid, endid from SplitLayout),
t3 as ( select st,startid s,Endid e, row_number() over(order by startid) rn from t1,t2)
select * from t3
pivot
(
max(substring(st,s,e)) str for rn in ([1],[2],[3])
) AS pvt
i am getting error near substring( open bracket;
Thanks!

Use a delimiter, you know how hard this excercise can become without one.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

yuvipoy SSChampion Points: 10469 More actions · Answer 3

'1010' is the input i need to split the input based on the startid, endid

startid, endid

1,1

2,1

3,1

4,2

6,3

so i need something like

1,0,1,0,11,123,........

need to split the string and insert into the table.

yuvipoy SSChampion Points: 10469 More actions · Answer 4

yuvipoy

SSChampion

Points: 10469

November 3, 2012 at 3:43 am

#1555744

Any suggestions!

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

yuvipoy (11/3/2012)
Any suggestions!

I can't speak for the others but I don't understand your narrative for this problem.

I recommend that you take a look at the article at the first link in my signature line to post some readily consumable data and try asking the question again. For example, I don't see how the narrative matches up with the data you posted for the following...

startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4
Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row 2+1=3 will be starting point for third row 3+1=4 will be starting point for fourth row 4+2=6 will be starting point for fifth row and so on ........

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

yuvipoy SSChampion Points: 10469 More actions · Answer 6

Say i'm having a string like this '101011123'

i need to split the sting based on my statid & endid

startid, endid

1,1

2,1

3,1

4,2

6,3

which needs to split something like

1,1---->1 starting position 1 and ending position 1 ,next starting id will be 1+1

2,1---->0 starting position 2 and ending position 1 ,next starting id will be 2+1

3,1---->1 starting position 3 and ending position 1 ,next starting id will be 3+1

4,2---->01 starting position 4 and ending position 2 ,next starting id will be 4+2

6,3---->112 starting position 6 and ending position 1 ,next starting id will be 6+3

9,.

.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

yuvipoy (11/4/2012)
Say i'm having a string like this '101011123'
i need to split the sting based on my statid & endid
startid, endid
1,1
2,1
3,1
4,2
6,3
which needs to split something like
1,1---->1 starting position 1 and ending position 1 ,next starting id will be 1+1
2,1---->0 starting position 2 and ending position 1 ,next starting id will be 2+1
3,1---->1 starting position 3 and ending position 1 ,next starting id will be 3+1
4,2---->01 starting position 4 and ending position 2 ,next starting id will be 4+2
6,3---->112 starting position 6 and ending position 1 ,next starting id will be 6+3
9,.
.
.
.

That's startposition and length, and it's significantly easier to resolve the elements out of your target string using this than the original method you proposed on another thread. Elements would initially resolve into rows of a column, which leaves you with the same problem as you had on that other thread - the maximum number of columns in a row when you pivot. Have you decided what to do about this?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

yuvipoy SSChampion Points: 10469 More actions · Answer 8

No, i am looking alternate method for pivot.

i have used another way like

DECLARE @input varchar(4000);

DECLARE @sql NVARCHAR(MAX)

select @input='101011123'

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

+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),

SUBSTRING(@input,(Startid ),(Endid )))+'''' FROM mytable

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

EXEC (@SQL)

where as it is taking more time ,so looking for alternate way!

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

yuvipoy (11/5/2012)
No, i am looking alternate method for pivot.
i have used another way like
DECLARE @input varchar(4000);
DECLARE @sql NVARCHAR(MAX)
select @input='101011123'
SELECT @sql = ISNULL(@SQL+N','+CHAR(10),'') +
+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),
SUBSTRING(@input,(Startid ),(Endid )))+'''' FROM mytable
SELECT @sql = N'SELECT' + CHAR(10) + @sql
EXEC (@SQL)
where as it is taking more time ,so looking for alternate way!

How many different record layouts would you like to handle automatically?

And, no... this actually isn't a pivot problem. It's simply a split of fix field format data. I can help you after I get home from work tonight.

In the meantime, it would be helpful if you could provide some readily consumable test data. Please see the first link in my signature line below for the best way to do that. Thanks.

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

yuvipoy SSChampion Points: 10469 More actions · Answer 10

How many different record layouts would you like to handle automatically?

i am having some 80 columns

And, no... this actually isn't a pivot problem. It's simply a split of fix field format data. I can help you after I get home from work tonight.

i have tried with pivot as well as the method i have posted,it seems that the method posted above by me is more faster than pivot, i'm looking for more faster process then the existing pivot and other method.

In the meantime, it would be helpful if you could provide some readily consumable test data.

this is mytable

startid, endid

1,1

2,1

3,1

4,2

6,3

input will be '101011123'

i need like

Select 1,0,1,01,112

if your still unclear,given me your understanding on this post i will try to correct them :exclamationmark:

Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

DECLARE

@MyInput VARCHAR(8000),

@MyOutput VARCHAR(8000)

SELECT

@MyInput = '101011123',

@MyOutput = ''

;WITH MyTable (startid, Length) AS (

SELECT 1,1 UNION ALL

SELECT 2,1 UNION ALL

SELECT 3,1 UNION ALL

SELECT 4,2 UNION ALL

SELECT 6,3

)

-- resolve the string elements into rows of a column

-- then concatenate with a comma in between

SELECT @MyOutput = @MyOutput + ',' + SUBSTRING(@MyInput, m.startid, m.Length)

FROM MyTable m

-- remove the leading comma

SET @MyOutput = STUFF(@MyOutput,1,1,'')

SELECT MyOutput = @MyOutput

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

yuvipoy SSChampion Points: 10469 More actions · Answer 12

ChrisM@Work (11/6/2012)
DECLARE @MyInput VARCHAR(8000), @MyOutput VARCHAR(8000) SELECT @MyInput = '101011123', @MyOutput = '' ;WITH MyTable (startid, Length) AS ( SELECT 1,1 UNION ALL SELECT 2,1 UNION ALL SELECT 3,1 UNION ALL SELECT 4,2 UNION ALL SELECT 6,3 ) -- resolve the string elements into rows of a column -- then concatenate with a comma in between SELECT @MyOutput = @MyOutput + ',' + SUBSTRING(@MyInput, m.startid, m.Length) FROM MyTable m -- remove the leading comma SET @MyOutput = STUFF(@MyOutput,1,1,'')
SELECT MyOutput = @MyOutput

You have given me the same method which i have used earlier

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

+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),

SUBSTRING(@input,(Startid ),(Endid )))+'''' FROM mytable

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

EXEC (@SQL)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

yuvipoy (11/5/2012)
No, i am looking alternate method for pivot.
i have used another way like
DECLARE @input varchar(4000);
DECLARE @sql NVARCHAR(MAX)
select @input='101011123'
SELECT @sql = ISNULL(@SQL+N','+CHAR(10),'') +
+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),
SUBSTRING(@input,(Startid ),(Endid )))+'''' FROM mytable
SELECT @sql = N'SELECT' + CHAR(10) + @sql
EXEC (@SQL)
where as it is taking more time ,so looking for alternate way!

It runs too fast to register in milliseconds:

DECLARE @input varchar(4000);

DECLARE @sql NVARCHAR(MAX)

select @input='101011123'

DECLARE @StartTime DATETIME

SET @StartTime = GETDATE()

;WITH MyTable (Rid, startid, Endid) AS (

SELECT 1,1,1 UNION ALL

SELECT 2,2,1 UNION ALL

SELECT 3,3,1 UNION ALL

SELECT 4,4,2 UNION ALL

SELECT 5,6,3

)

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

+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),

SUBSTRING(@input,(Startid ),(Endid )))+''''

FROM mytable

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

--print @sql

EXEC (@SQL)

SELECT DATEDIFF(ms, @StartTime, GETDATE())

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

yuvipoy (11/5/2012)
this is mytable
startid, endid
1,1
2,1
3,1
4,2
6,3

Is this structure the same or different for each string of numbers you have to process?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
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