Solved

SQL Select: display only the first 800 characters from line 231 in a cell

Posted on 2010-08-17
19
539 Views
Last Modified: 2012-05-10
The table FormDefinitions is as follows:

(<xaoFormID, char(75),>
 ,<xaoControlName, char(70),>
,<xaoClassID, char(35),>
,<xaoType, numeric(1,0),>
,<xaoProperties, text,>
,<xaoCode, text,>):

xaoCode contains over 900 lines of VBScript code. So doing a basic SQL select isn't very helpful when I only want to view only parts of the VBScript code.

Is it possible to get SQL Server 2008 to display the xaoCode values of the first 800 characters from line 231 ?

Thanks!

0
Comment
Question by:jeremyll
  • 9
  • 9
19 Comments
 
LVL 15

Expert Comment

by:MohammedU
Comment Utility
SQL server can check based on line number in a column data...
You can use the SUBSTRING function but you should know start and end points to select...
If you know perticular word or character then you can CHARINDEX funcition.

Check SQL Server Books online for help about SUBSTRING and/or CHARINDEX
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
First create the function in the code box, then use this query

select F.*, R.First800FromLine231
from FormDefinitions F
outer apply (
  select left(one, 800) as First800FromLine231
  from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
  where id = 231) R
CREATE function dbo.multiRow(@data varchar(max), @delims varchar(10))

returns table as return

with cte (id,one,rem)

as

(

select

	1,

	rtrim(ltrim(LEFT(@data, charindex(@delims,@data+@delims)-1))),

	substring(@data, charindex(@delims,@data+@delims)+LEN(@delims+'.')-1, LEN(@data))

where nullif(@data,'') is not null

union all

select

	id+1,

	rtrim(ltrim(LEFT(rem, charindex(@delims,rem+@delims)-1))),

	substring(rem, charindex(@delims,rem+@delims)+len(@delims+'.')-1, LEN(rem))

from cte

where nullif(rem,'') is not null

)

select id=row_number() over (order by id), one from cte where nullif(one,'') is not null

GO

Open in new window

0
 

Author Comment

by:jeremyll
Comment Utility
Thanks CyberKiwi,

I created the function in the code box above, but when I ran the select statement I got the error message:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
sqlError.gif
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
In your query that uses the function, add this, e.g.

select f.*..
from..
.
.
.
where id=231) R
option (maxrecursion 4000) --- add this line, assuming 4000 is enough!
0
 

Author Comment

by:jeremyll
Comment Utility
Thanks CyberKiwi,

The level of SQL here is a bit beyond me at the moment as I'm kind of a beginner. But, I'm trying to understand it at the same time. :-)

I want to apply a filter to the select statement you gave me:

select F.*, R.First800FromLine231
from FormDefinitions F
outer apply (
  select left(one, 800) as First800FromLine231
  from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
  where id = 231) R
option (maxrecursion 4000)

I want to filter the results so that the select statement will output results where FormDefinitions.xaoFormID like ‘fsi%’

So I tried the following select statement below. SQL Server didn’t like the last line.

select F.*, R.First800FromLine231
from FormDefinitions F
outer apply (
  select left(one, 800) as First800FromLine231
  from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
  where id = 231) R
option (maxrecursion 4000)
where FormDefinitions.xaoFormID like ‘fsi%’
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
select F.*, R.First800FromLine231
from FormDefinitions F
outer apply (
   -- the main query is below
  select left(one, 800) as First800FromLine231
  from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
  where id = 231
    and FormDefinitions.xaoFormID like ‘fsi%’
   -- the main query is above
) R
option (maxrecursion 4000)

The outer query is there only to extract the first 800 chars from line 231
0
 

Author Comment

by:jeremyll
Comment Utility
I tried that, but it is returning a very long list of results with some of the following xaoFormID values:

FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESATLA09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      
FDESCOL09-REV-A                                                      

which doesn't follow the where clause:
...
where id = 231
    and FormDefinitions.xaoFormID like ‘fsi%’

This SQL statement took about 1 minute to complete and returned over 15,000 rows. It should be taking a second and returning 1000 rows for a where clause such as where FormDefinitions.xaoFormID like ‘fsi%’
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
You are right - I am not even reading it properly...

select F.*, R.First800FromLine231
from FormDefinitions F
outer apply (
   -- the inner query is only used to extract row 231
  select left(one, 800) as First800FromLine231
  from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
  where id = 231
) R
WHERE FormDefinitions.xaoFormID like 'fsi%'
option (maxrecursion 4000)
0
 

Author Comment

by:jeremyll
Comment Utility
Thanks CyberKiwi,

That's pretty close, but after executing the above Select, the results in the xaoCode are returning only one line of code. So it doesn't matter whether you specify the first 800 or 8000 characters.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:jeremyll
Comment Utility
I would like to display all the 800 characters which could be over 50 lines of code.

Thanks!
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
I really don't understand.
You want line 231 right? The first 800 characters from it? - it does exactly that.

"So it doesn't matter whether you specify the first 800 or 8000 characters."
So what you are saying is that line 231 never has more than 800 characters right?  But the code is still doing what it is supposed to do.

What is special about line 231?
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
Okay, I think you mean "from line 231 onwards"
Let me come back to you
0
 

Author Comment

by:jeremyll
Comment Utility
Yes thats what I meant! Sorry, if I didn't make myself clearer before.
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
It won't be fast, that's for sure!

select
      convert(xml,(
        select one+Char(10)
        from dbo.multiRow(convert(varchar(max),F.xaoCode), Char(10))
        where id >= 231
        for xml path(''), root('xm')
      )).value('xm[1]', 'varchar(800)')
from FormDefinitions F
WHERE F.xaoFormID like 'fsi%'
option (maxrecursion 4000)
0
 

Author Comment

by:jeremyll
Comment Utility
I tried the above select statement, but it only returned three rows with NULL values in one column.
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
Can you manually verify that those records do have more than 231 lines?
0
 

Author Comment

by:jeremyll
Comment Utility
I can verify that for

where xaoFormID like 'fsi%'

the following values for xaoFormID:
FSIPM                                                      
FSIPNIAG                              
FSIPCABR                              
FSIPARIZ                              
FSIPVSTR                              
FSIPAQUA                              
FSIPECLI                              
FSIGMILL                              
FSIGBIGS                              
FSIGMIAM                              
FSIPCADI                              
FSIPEDEN                              
FSIPWSTR                              

all above have xaoCode values that have more than 231 lines.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
I give up on not using a function, in fact I believe using a helper function will be a lot faster.
First create the function in the code box.
Then use this query:


select      F.*,
            convert(nvarchar(800),dbo.skipXtimes(F.xaoCode, CHAR(10), 230))
from FormDefinitions F
WHERE F.xaoFormID like 'fsi%'

CREATE function dbo.skipXtimes(@str nvarchar(max), @char char(1), @times int)

returns nvarchar(max)

as

begin

declare @test int

while @times > 0

begin

set @test = charindex(@char,convert(nvarchar(4000),@str))

if @test>0

	select @str=stuff(@str,1,@test,''), @times=@times-1

else

	set @times=0

end

return @str

end

GO

Open in new window

0
 

Author Closing Comment

by:jeremyll
Comment Utility
LEGEND!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now