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: 453
  • Last Modified:

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!
0
jeremyll
Asked:
jeremyll
  • 2
1 Solution
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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