Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-08-17
19
Medium Priority
?
593 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

885 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