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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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  
 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.04 seconds. Powered By: Snitz Forums 2000