Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 char index help

Author  Topic 

blummy99
Starting Member

5 Posts

Posted - 2013-03-27 : 17:28:27
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

30421 Posts

Posted - 2013-03-27 : 17:55:32
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 - 2013-04-10 : 10:52:41
Yes, that is correct. Some rows won't have an employee message. How can I get around this?
Go to Top of Page
   

- Advertisement -