SQL Select: display only the first 500 characters from the start of the line where code like '%'Determine part ID for ...%'

Thanks CyberKiwi! I have a similar question…

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.

I would like to get SQL Server 2008 to display the xaoCode values from the start of the line…

where like ‘%'Determine part ID for Product version and installation - for base price lookup%’

and onwards for the first 500 characters

Thanks!
jeremyllAsked:
Who is Participating?
 
cyberkiwiCommented:
Try this
with tmp as (select *, xaoCodeVC = CONVERT(varchar(max), xaoCode) from FormDefinitions)
select *, first500ofX= SUBSTRING(xaoCodeVC,
	PATINDEX('%Determine part ID for Product version and installation - for base price lookup%', xaoCodeVC), 500)
from tmp
where xaoCode like '%Determine part ID for Product version and installation - for base price lookup%'

Open in new window

0
 
cyberkiwiCommented:
Sorry correction.
You really should look at changing the TEXT columns to VARCHAR(MAX) ...
;with tmp as (
	select xaoFormID, xaoControlName, xaoClassID, xaoType, xaoProperties, xaoCodeVC = CONVERT(varchar(max), xaoCode)
	from FormDefinitions
	-- WHERE CLAUSE HERE
)
select xaoFormID, xaoControlName, xaoClassID, xaoType, xaoProperties, first500ofX= SUBSTRING(xaoCodeVC,
	PATINDEX('%Determine part ID for Product version and installation - for base price lookup%', xaoCodeVC), 500)
from tmp
where xaoCodeVC like '%Determine part ID for Product version and installation - for base price lookup%'

Open in new window

0
 
jeremyllAuthor Commented:
TOO GOOD!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.