Solved

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

Posted on 2010-08-17
19
541 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
ID: 33460608
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
ID: 33460622
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
ID: 33461102
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
ID: 33461344
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
ID: 33481129
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
ID: 33481154
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
ID: 33481516
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
ID: 33481523
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
ID: 33481790
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:jeremyll
ID: 33481822
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
ID: 33481860
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
ID: 33481871
Okay, I think you mean "from line 231 onwards"
Let me come back to you
0
 

Author Comment

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

Expert Comment

by:cyberkiwi
ID: 33481962
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
ID: 33482127
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
ID: 33482180
Can you manually verify that those records do have more than 231 lines?
0
 

Author Comment

by:jeremyll
ID: 33482550
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
ID: 33483204
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
ID: 33497342
LEGEND!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

930 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

11 Experts available now in Live!

Get 1:1 Help Now