Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

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

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
jeremyll
Asked:
jeremyll
  • 9
  • 9
1 Solution
 
MohammedUCommented:
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
 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
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
 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
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
 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
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
 
jeremyllAuthor Commented:
I would like to display all the 800 characters which could be over 50 lines of code.

Thanks!
0
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
Okay, I think you mean "from line 231 onwards"
Let me come back to you
0
 
jeremyllAuthor Commented:
Yes thats what I meant! Sorry, if I didn't make myself clearer before.
0
 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
I tried the above select statement, but it only returned three rows with NULL values in one column.
0
 
cyberkiwiCommented:
Can you manually verify that those records do have more than 231 lines?
0
 
jeremyllAuthor Commented:
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
 
cyberkiwiCommented:
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
 
jeremyllAuthor Commented:
LEGEND!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now