March 28, 2007 at 8:58 am
I have a table with an ntext field that I need to pull only the first six lines of data (up until the 6th char(13) carriage return). Does anybody have any ideas on how I would accomplish this?
The first 6 lines will be of varying lengths and everything after the sixth carriage return would be ignored.
Here is an example of what the data might look like:
Safety -
Environmental -
PPE -
Vessel Entry -
Lockout -
Tagout -
blah, blah, blah - to be ignored
blah, blah, blah - to be ignored
blah, blah, blah - to be ignored
March 28, 2007 at 10:01 am
Not sure you can do this in a set based method. Are you needing to do this across multiple rows returned by a signle query? If so and you can be sure the 6th chr(13) will be before the 8000th character then try converting NTEXT to VARCHAR(8000) and submit the a function to return the value like you want but if you can't be sure then might not be possible as you want.
March 28, 2007 at 11:10 am
Antares686 raised a lot of good points. Here's an idea: it looks like the first 6 lines are a header, adn have the same format....so for example, does line 6 always start with 'Tagout -'? if it does, from that point, you can find the charindex of the next CrLf after that point, and take the LEFT of the string to that point.
if that doesn't work, you can do the repetitive CHARINDEx of CHAR(13) + CHAR(10) 6 times...annoyingly long, and would probably affect performance.
if the stuff works below, you could add a calculated column with the SQL below as the function, and might work out for you.
here's a prototype that works:
create table test(testid int identity(1,1),Ndata NTEXT)
insert into test(ndata) values (N'
Safety - stuff between preamble and CrLf
Environmental - stuff between preamble and CrLfstuff between preamble and CrLf
PPE -stuff between preamble and CrLfstuff between preamble and CrLfstuff between preamble and CrLf
Vessel Entry -stuff between preamble and CrLfstuff between preamble and CrLfstuff between preamble and CrLf
Lockout - stuff between preamble and CrLfstuff between preamble and CrLfstuff between preamble and CrLf
Tagout - stuff between preamble and CrLfstuff between preamble and CrLfstuff between preamble and CrLf
blah, blah, blah - to be ignored
blah, blah, blah - to be ignored
blah, blah, blah - to be ignored ')
--does it convert?
SELECT CONVERT(varchar(8000),ndata) as results from test
--'does the data even have a vbCrlf?
SELECT CHARINDEX(CHAR(13) + CHAR(10),CONVERT(varchar(8000),ndata),3) as results from test
--can we find the charindex?
SELECT CHARINDEX('Tagout -',CONVERT(varchar(8000),ndata)) as results from test
--can we find the charindex of vbCrLf after the Tagout?
SELECT CHARINDEX(CHAR(13) + CHAR(10),CONVERT(varchar(8000),ndata),CHARINDEX('Tagout -',CONVERT(varchar(8000),ndata))) as results from test
--now the results before that character?
SELECT LEFT(CONVERT(varchar(8000),ndata),CHARINDEX(CHAR(13) + CHAR(10),CONVERT(varchar(8000),ndata),CHARINDEX('Tagout -',CONVERT(varchar(8000),ndata)))) from test
--would this work?
ALTER TABLE WHATEVER ADD CALCHEADER AS LEFT(CONVERT(varchar(8000),ndata),CHARINDEX(CHAR(13) + CHAR(10),CONVERT(varchar(8000),ndata),CHARINDEX('Tagout -',CONVERT(varchar(8000),ndata))))
Lowell
March 28, 2007 at 11:19 am
Unfortunately, I can't count on the last line starting with TAGOUT. The only thing for sure is that the first six lines are what I need however a user types them. The other problem is that the database cannot be modified since it is a prepurchased package application and I cannot modify the structure in anyway.
Here is what I am playing with that seems to be working - feel free to critique.
DECLARE @CRLF char(1),
@Count int,
@New1 varchar(2000),
@Old varchar(2000),
@CharIndex int,
@Working varchar(2000)
SELECT @Old = (select CAST(txtext as varchar(2000)) FROM eqpmas where eqpnum = 'N-031-01650')
SET @Working = @Old
SET @CRLF = char(13)
SET @Count = 1
SET @New1 = '' --Initialize new string
WHILE @Count <= 6
BEGIN
SET @CharIndex = CHARINDEX(@CRLF, @Working) --Find the starting index of the CRLF
SET @New1 =@New1 + substring(@Working, 1, @CharIndex + LEN(@CRLF)-1) --CHARINDEX returns starting position of @CRLF which is two characters. To get to the end of the search string, subtract 1 from length of @CRLF
SET @Working = SUBSTRING(@Working, @CharIndex + LEN(@CRLF), LEN(@Working)) --Delete found portion of @Working string
SET @Count = @Count + 1 --Increment the counter
END
select @New1 as 'Safety1'
March 28, 2007 at 11:54 am
the charindex(... 6 levels deep is incredibly slow with no execution plan...on my test table with 2 rows, it took 41 seconds...41!!! on the first pass as an execution plan was created...second pass was instantaneous.
technically this works for reference: leave out the red WHERE statement, and consider creating this as a VIEW so that you can access and join to it better. since a VIEW is just a saved query, and does not affect the underlying data, you wouldn't affect your 3rd party licensing scheme....but i'm not a lawyer either, so my opinion means nothing
SELECT EQPNUM,
LEFT(TXTEXT,
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--6th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--5th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--4th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--3rd vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT, --2nd vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT --1st vbCrLf
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
) + 2 --because CrLf is 2 chars
) --left
) AS TXTEXT
FROM
(
--this used my example
SELECT TESTID AS EQPNUM,CONVERT(VARCHAR(8000),NDATA) AS TXTEXT from test
--this is your tablenames i think
--SELECT EQPNUM,CONVERT(VARCHAR(8000),TXTEXT) AS TXTEXT FROM EQPMAS where eqpnum = 'N-031-01650'
) X
Lowell
March 28, 2007 at 12:46 pm
Thanks Lowell. I am not sure what that means when the first time through it took 41 seconds and the second time through was instantaneous. What I had planned on doing is replacing the hardcoded value in red with a parameter. I would turn this into a SP and pass in the key value. That is fast and there is only one fetch to the DB to store the original text into a declared variable.
Since I only need the first six lines, do I really need to convert to a varchar(8000)?
March 28, 2007 at 12:50 pm
what a meant was i would do this:
CREATE VIEW VW_EQPMAS AS
SELECT EQPNUM,
LEFT(TXTEXT,
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--6th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--5th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--4th vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT,--3rd vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT, --2nd vbCrLf
CHARINDEX(CHAR(13) + CHAR(10),TXTEXT --1st vbCrLf
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
)+ 2 --because CrLf is 2 chars
) + 2 --because CrLf is 2 chars
) --left
) AS TXTEXT
FROM
(
--this is your tablenames i think
--SELECT EQPNUM,CONVERT(VARCHAR(8000),TXTEXT) AS TXTEXT FROM EQPMAS
) X
then a stored procedure example would query the VIEW, and not use thesql at all:
CREATE PROCEDURE PR_EQP (@EQPNUM VARCHAR(18) ) AS
SELECT * FROM VW_EQPMAS WHERE EQPNUM=@EQPNUM
because the VIEW would have created the query plan, the procedure would benefit and be quick.....
Lowell
March 28, 2007 at 12:55 pm
oh and the 41 second thing? try it yourself....paste my query and run it...it will be slow the first time...SQL Server will save a plan for that query in case it gets it again, so run the same query a second time....FAST!...add a parameter to it...fast....it's just the first time ti gets executed, or if it has not been run in a long time.
if it is part of a stored proc or a view, the plan gets saved, and is faster on subsequent calls.
Lowell
March 28, 2007 at 6:00 pm
Use my function from the post over there:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=338231
Use CHAR(13) + CHAR(10) as a delimiter.
Return result set where line No <=6
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply