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.
| 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=falseI 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 Mwhere 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" |
 |
|
|
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? |
|
|
|
|
|
|
|
|
char index help