SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 char index help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blummy99
Starting Member

5 Posts

Posted - 03/27/2013 :  17:28:27  Show Profile  Reply with Quote
Hello,

I need to get the Employee message part of the field below:

Adams, Julie J has submitted a Request for time off. Request Details: Accrual balances: Floating Holiday: Personal: Vacation: 160:00 Leave Type: Personal Start Date: 3/15/2013 End Date: 3/15/2013 Employee's message: partial day off Go to your task list to take immediate action. Click here for Self-Service: http://server/yyy/applications/ems/html/Inbox.jsp?ess=true Click here to return to application: http://server/yyy/applications/ems/html/Inbox.jsp?ess=false


I tried using this sql but it gives me an error:

SELECT SUBSTRING(M.MSGCONTENTTXT, CHARINDEX('Employee''s message: ', M.MSGCONTENTTXT) + 20,
CHARINDEX('Go to your task list to take immediate action. ', M.MSGCONTENTTXT) - (CHARINDEX('Employee''s message: ', M.MSGCONTENTTXT) + 20)) AS 'NOTES'
FROM MESSAGING M
where M.MSGSUBJECTDSC LIKE ('Request for Time off%')


The error is: Invalid length parameter passed to the LEFT or SUBSTRING function.

Can someone tell me what I did wrong or is there a better way to do this? Also, not every record will have a message. In that case, there will be a blank space after Employee's message:

Thanks!

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 03/27/2013 :  17:55:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
One of the charindex functions returns a zero value, and when you substract enough to calculate a negative value (ex 0 - 99 + 20), that negative value is not allowed in LEFT/SUBSTRING/RIGHT function.

It means that one row does not have the "Employee's message" text.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

blummy99
Starting Member

5 Posts

Posted - 04/10/2013 :  10:52:41  Show Profile  Reply with Quote
Yes, that is correct. Some rows won't have an employee message. How can I get around this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000