Invalid length parameter passed to the SUBSTRING function

Hi there,

I'm using SQL Server 2005 and when I do this

Declare @ContactName varchar(50)
Set @ContactName = 'This is a long description - Use Fee'
Select LEFT (@ContactName, CHARINDEX('-', @ContactName) - 1)

I'm getting  This is a long description

Which is ok but
When I'm doing

select Left(Descr,CHARINDEX('-',Descr)-1) as Descr from inventory

I'm getting error
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

I have a description column with "-" which I don't want to show everything 
from the RIGHT side of the "-"

P.S. this line of code is in my view.

Thanks,
Ed Dror

0
Ed
11/25/2009 5:08:42 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
5908 Views

Similar Articles

[PageSpeed] 27

The problem is that you have a description value that does not contain a '-'. Try this:

SELECT CASE WHEN CHARINDEX('-', descr) > 0
             THEN LEFT(descr, CHARINDEX('-', descr) - 1)
             ELSE descr
        END AS descr
FROM inventory;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
11/25/2009 6:50:29 PM
Plamen,

Thanks! it works

Thanks you.
Ed

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:momdnQ0CIcnw4ZDWnZ2dnUVZ_qBi4p2d@speakeasy.net...
> The problem is that you have a description value that does not contain a 
> '-'. Try this:
>
> SELECT CASE WHEN CHARINDEX('-', descr) > 0
>             THEN LEFT(descr, CHARINDEX('-', descr) - 1)
>             ELSE descr
>        END AS descr
> FROM inventory;
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 

-1
Ed
11/25/2009 7:12:54 PM
You need to figure out which of your Descr entries doesn't have a hyphen in 
it with a query like this:

SELECT Descr
FROM inventory
WHERE CHARINDEX('-',Descr) = 0;

Then you'll need to either fix the Descr data or modify your query to handle 
the cases where no hyphen exists in the column.

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Ed Dror" <ed.dror@bluepoint.com> wrote in message 
news:40732006-4581-4B95-BB6D-D459D56B6F1B@microsoft.com...
> Hi there,
>
> I'm using SQL Server 2005 and when I do this
>
> Declare @ContactName varchar(50)
> Set @ContactName = 'This is a long description - Use Fee'
> Select LEFT (@ContactName, CHARINDEX('-', @ContactName) - 1)
>
> I'm getting  This is a long description
>
> Which is ok but
> When I'm doing
>
> select Left(Descr,CHARINDEX('-',Descr)-1) as Descr from inventory
>
> I'm getting error
> Msg 536, Level 16, State 5, Line 1
> Invalid length parameter passed to the SUBSTRING function.
>
> I have a description column with "-" which I don't want to show everything 
> from the RIGHT side of the "-"
>
> P.S. this line of code is in my view.
>
> Thanks,
> Ed Dror
> 

-2
Michael
11/30/2009 5:39:39 AM
Reply:

Similar Artilces:

Time functions #2
Hi, I have to do some time calculations and I have a formula that outputs in minutes and I have to subtract a standard time to this number of minutes. For example, I have 8:00 AM in one cell and 20.68 minutes on an other cell. The output that I am expecting is 7:39 AM. Does anybody know how to bring an output like that by subtracting the minutes? Please help. Thanks With 8:00 AM in A1 and 20.68 in B1, use this formula =A1-B1/(24*60) Why does it work? Because Excel stores time as a fraction of a day. We convert minutes to days by dividing by 24*60 (24 hr/day and 60 min/hr) You could just u...

Adding functionality to MS Outlook Express
Hi, I am looking for help to integrate anti-spam open source programs with MS Outlook Express :- - SpamPal - http://www.spampal.org/ - Spamato - http://www.spamato.net/ The problem is to provide a library that will facilitate this. I have read the MSDN Messaging and Collaboration documentation which gives the ability to get notifications of new messages and to access message folders :- http://msdn2.microsoft.com/en-gb/library/ms709546.aspx The next need is to add a toolbar (with buttons for SPAM/NOSPAM) and to detect what messages are selected in the message window. The MSOE...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

Report Asking For a Parameter
So I had a report that called with its Record Source set to TableA. I then made a copy of this report, but change the Record Source to TableB. TableB is a summary of TableA, and is missing one field, NAME So when I run my report, it pops up asking for the paramater for NAME. I looked at the report and deleted the feild that was linked to NAME. The report is still asking for NAME, even though no feilds on the report ask for it now. How do I go about fixing this? On Mon, 3 Mar 2008 15:19:03 -0700, "James" <fake@email.com> wrote: NAME (which by the way is a Reserved Wor...

If Function Possibilities
Is there a way to set up in a cell A1, something like... If the formula used in cell B1 is "here I would type the actual formula in," then display the words "works," else display "mistake" ? -- rheller ------------------------------------------------------------------------ rheller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24081 View this thread: http://www.excelforum.com/showthread.php?threadid=377045 That's a pretty cryptic post. Do you mean: In A1: =IF(B1=n,"Works","Mistake") where 'n...

Writing Access functions
I am an old FoxPro programmer (but have been out of programming for many years) and I have been through the Access basics that I have been able to find on line. So I can create tables, forms enter data the basics. I am learning VBA no problem there. Now I want to write some functions. I would like to write one function that is passed an ID and returns the last name of the person having that ID. I would like to write this function in two ways: 1. Connect to a table in my DB find the ID and return the name 2. Have the function perform a query that returns a record set from which t...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Function Keys in Excel
Can someone please tell me what the F2 key does in Excel 2000? I have a column of data which has been copied from Access 2000 into an Excel 2000 sheet. It is numeric data and when I use the "Countif" function on this data, it doesn't calculate. However, if I press the "F2" function key in any of the cells, something happens to the data so that the "countif" function recoginises it. Can anyone explain what is happening here? Thank you. -- LPS F2 allows you to edit directly in the cell, do you have calculations set to automatic under tools>calcul...

Error 1327 Invalid drive e:\
I upgraded to Windows 7 and it redesignated my two CD-ROM drives as E: and F:. I changed them to F: and G: and curretnly have no E:\ drive. Whenever I tray to launch Word 2007, it goes through a "Configuring Microsoft Office Enterprise 2007" procedure, then stops about a third of the way through and gives me the subject error message and tells me to run "Repair". When I click out of the error message boxes, it finally loads Word. I tried running "repair" and even removing Office 2007 to reinstall it, but I get the same error message and it stops ex...

outlook parameters #2
This is a multi-part message in MIME format. ------=_NextPart_000_006C_01C60558.781A0570 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone!! there is some place where I can see all thje outlook parameters?? like a = /safe=20 Jana ------=_NextPart_000_006C_01C60558.781A0570 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"...

faq: type of CArray as function parameter create errors
Hi, class CMyPoint { public: long x; long y; CArray<int,int&> m_intSegmentNo; CMyPoint(); virtual ~CMyPoint(); CMyPoint(const long &xx, const long &yy); CMyPoint(const CMyPoint &cmpt); CMyPoint(const CPoint &cpt); CMyPoint& operator = (const CMyPoint &assmypt); CMyPoint& operator = (const CPoint &asspt); bool operator == (const CMyPoint &rmypt);//const CMyPoint &lmypt, bool operator != (const CMyPoint &rmypt);//const CMyPoint &lmypt, }; .... class CSegment { public: CList<CMyPoint,CMyPoint&> m_PointList; CSeg...

Function for convertion of leters doesn't work.
I'm using function LOWER to convert string i my table on small size but Access gives me a message that the function is not defined.Please, do you know another function? MS Access does not have a LOWER function. Try LCase() instead. -- Cheryl Fischer Law/Sys Associates Houston, TX "Stenli" <svet2000@seznam.cz> wrote in message news:10b201c3626e$b613a050$a301280a@phx.gbl... > I'm using function LOWER to convert string i my table on > small size but Access gives me a message that the function > is not defined.Please, do you know another function? ...

Report Writer Functions #2
Where can I find instructions and examples on the various report writer functions? I've installed the SDK but I don't see anything there. The Report Writer On-line manual (Help - Printable Manuals - Reporting - Report Writer) has a fairly good description of the functions in Chapter 6. Do you look there or is there a specific function that you did not find there for which you are searching? "Elaine" wrote: > Where can I find instructions and examples on the various report writer > functions? I've installed the SDK but I don't see anything there. Hi El...

Public Function not running
Hey, I have a public function that I call from a cell. The function is: Public Function QuitTime(strQuitTime As String) As Date Dim strHour, strMin As String Dim intHour, intMin As Integer Dim dtTime As Date strHour = Left(strQuitTime, 2) strMin = Right(strQuitTime, 2) dtTime = strHour & ":" & strMin dtTime = dtTime + Range("MainSheet!B2") ' this cell contains the value today() QuitTime = dtTime End Function Anyway, I run a vlookup on this array from another sheet and when ever i run that vlookup, it makes all the cells in th...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...

Outbox Parameter Value Invalid Error
My Gmail is linked to Outlook. Currently, I have a message sitting in the Outbox of email. It won't send after multiple tries, and the error message reads: Task 'Gmail - Sending' reported error (0x80070057) : 'Could not complete the operation. One or more parameter values are not valid.' What does this mean? I am not a very savvy IT person, so layman's terms would be helpful with any offered solutions. Thanks! ...

::PostMessage not delivering the parameter
I have a PostMessage call crossing thread boundaries, but the parameter passed doesn't seem to be right. What could be wrong? Code: -------------------- CClientThread* pThread = (CClientThread*) AfxBeginThread(RUNTIME_CLASS(CClientThread), THREAD_PRIORITY_NORMAL, 0, CREATE_SUSPENDED); if (!pThread) { clientSock.Close(); TRACE("Couldn't create thread\n"); AfxMessageBox("Couldn't create thread\n"); return; } //Keep track of all threads m_ClientThreads.push_back(pThread); // Pass the socket to the thread by passing the socket handle. // You...

(Advance Programming) Parameter Returned as Value
Hi, I wish some body could Help Me, I have being loocking for a solution for 2 days. I need to make a simple Query to check if the parameter values are in a Table. I want the Query to return "Founds" and "Not Founds". For Example: Table Definition: TName CREATE TABLE [dbo].[TName] ( [IdName] [bigint] IDENTITY (0, 1) NOT NULL , [Name] [nvarchar] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ) ON [PRIMARY] Values: Insert Into TName (Name) Values ('Paul') Insert Into TName (Name) Values ('Peter') Insert Into TName (Name) Valu...

Missing engineering functions
In an earlier version of Excel, I had "engineering functions" like =dec2hex and =hex2dec. Not, in Excel 2003, I see them in the "help files' but not in my list of available formulas. How do I get them to work in Excel 2003? Thanks in advance, Geoff Waters Glendale, CA Geoff, I don't have Excel 2003, but you should be able to go to Tools | Add-ins and check mark "Analysis Took Pak" in the list that appears. Regards, Jim Cone San Francisco, CA "Geoff" <grw888@hotmail.com> wrote in message news:zwfIc.299$Qu5.238@newsread2.news.pas.earthli...

Read Receipt Function in outlook
The read receipt option is no longer working in Outlook Small Business Edition for XP 2002. I have not made any recent changes to my outlook setup. Does anyone know how I can trouble shoot this? ...

Indirect and Address Functions to Determine Schedule #3
I am struggling with writing an Excel formula that I would greatl appreciate some help on. In theory this sounds quite simple. I have a table which shows th progression it takes, in months, to move from one 'level' to the next. Another larger table shows how a number of 'starting' individuals woul progress through their levels based on the progression table mentione earlier. e.g. [FONT=courier new]Progression Table[/FONT] ----------------------- Months Level One 1 Level Two 2 Level Three 3 etc. Duration Table # of People Jan Feb M...

Multiple "SUM IF" functions in one formula??
I know it can be done, Excel can do anything but I'm not sure if I can even explain it. For example, I want column D2 to display a qty IF a customer number equals a specific value AND a part number equals a specific value. Basically Column A is a list of part numbers, and Row 1 is a list of customers. I need to pull how many parts each customer has ordered. Hi see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "Dax" <dax.tipton@smc.com> schrieb im Newsbeitrag news:593e3ca7.0408301337.7b8f6a35@posting.google.com... > I ...

Outlook today function
When I click on the customize outlook today button nothing happens. What should I do. OL2000: You Cannot Customize Outlook Today After You Install Critical Update 813489 for Internet Explorer: http://support.microsoft.com/default.aspx?scid=kb;EN-US;820575 You can edit the registry to make changes to Outlook Today. http://www.outlook-tips.net/howto/oltoday.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://ww...

apply function to multiple cells
Hi, So I want to write a function which divides every value in column A b the corresponding value in column B and I don't feel like monkey typin the formula in every cell. How can I apply this formula to all cells? I want column C to look like this. Do I need to write a loop o smthing? A1/B1 A2/B2 A3/B3 Thank -- MrXX ----------------------------------------------------------------------- MrXXX's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3124 View this thread: http://www.excelforum.com/showthread.php?threadid=50916 In cell C1 enter the formula: =A1/...

Auto delete stopped functioning in Outlook 2000
Hi, I have a single install on a user PC which although the option to auto empty the waste bin is checked it is not deleting or even asking whether she is sure she wants to delete. Advice appreciated, Sam ...