Substring Function Error.....
Jul 29, 2003
select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 0)
from msdb.sysjobs.name
name
-------------------- ----------- ----------- -----------
Job_4927_2_7Sun 30 9 4927_2_7S
Job_250144_2_6Sat 34 11 250144_2_6S
Job_30197_2_1Mon 32 10 30197_2_1M
but when I use following - 3)
select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 3)
from msdb.sysjobs.name
I get the result I want (last column):
Job_4927_2_7Sun 30 9 4927_2
Job_250144_2_6Sat 34 11 250144_2
Job_30197_2_1Mon 32 10 30197_2
but also with an error:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
thanks
David
View 3 Replies
ADVERTISEMENT
Sep 28, 2007
Hi,
I am using a simple procedure to pivot results (found in another forum and adapted it). It is done on SQL Server 2005 server with all service packs. Procedure:
**************
ALTER Procedure [dbo].[EthnicityPivot] @StDate as Datetime, @EndDate as Datetime
as
begin
DECLARE @Teams varchar(2000)
truncate table ForEthnicPivot
INSERT INTO ForEthnicPivot
SELECT DISTINCT COUNT(ID), Team, Ethnicity
FROM dbo._EthnicityByTeamEpisode
where Startdate between @StDate and @EndDate
GROUP BY Ethnicity, Team
SET @Teams = ''
--// Get a list of the pivot columns that are important to you.
SELECT @Teams = @Teams + '[' + Team + '],'
FROM (SELECT Distinct Team FROM ForEthnicPivot) Team
--// Remove the trailing comma
SET @Teams = LEFT(@Teams, LEN(@Teams)-1)
--// Now execute the Select with the PIVOT and dynamically add the list
--// of dates for the columns
EXEC( 'SELECT * FROM ForEthnicPivot PIVOT (SUM(countID) FOR Team IN (' + @Teams + ')) AS X' )
end
************
I can call the function:
exec EthnicityPivot '01/01/2007','09/09/2007'
and it works fine in SQL analyzer, but when I want to use it in Visual Studio in a new report I am getting this error
message:
There is an error in the query. Invalid length parameter passed to the SUBSTRING function. Incorrect syntax near ')'.
Anyone had similar error and sorted it?
Cheers
Polda
View 4 Replies
View Related
Nov 28, 2005
Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(__CustomerMachines002.folder_id = @folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
View 7 Replies
View Related
Nov 12, 2003
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error:
"Invalid length parameter passed to the substring function."
Here's the code in question:
orderConnection.Open()
orderReader = orderCommand.ExecuteReader()
setControls(orderReader)
...
Private Sub setControls(ByVal dr As SqlDataReader)
If (dr.Read()) Then '<--*******problem line*******
The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?
View 3 Replies
View Related
Jun 20, 2008
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30))
insert into @t
select '2' union all
select '[5] [4]' union all
select ' [12] [1]'
so i need to get...
col
-------
2
5
12
EDIT-
OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30))
insert into @t
--select '2'union all
select '[5] [4]'union all
select ' [12] [1]'
select col
,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1)
from @t
Em
View 20 Replies
View Related
Mar 16, 2001
hi thanks for your help
update vendor_invoice
set de_ftpdownload= (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice)
where vendor_invoice_id =931
This get me this error.... so what is the max allowable length of substring function.
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
Thanks
Al
View 1 Replies
View Related
Jun 20, 2004
Hello,
I want to select the characters before "-" from Column A.
For example:
12345-HSC
222-ABC
I'd would like to select it as:
12345
222
Please Help!!!!!!!!!!!!!!!!!!!!! :confused:
View 3 Replies
View Related
Oct 11, 2005
Harshi writes "Hello SQL Team!!
I have a problem in Substring function. There is a Text data type field in my Table and it has large no of characters. I want to get the whole text as the result. The problem is its just giving me 300 characters from a row, as the result.
My statement as follows;
//============================================================
SELECT a.L2Topic, a.TopicIndex, a.L1Topic,
Substring(b.Des, 1, 900), b.PriceGroup, b.Price,
b.L1TopicIndex, b.Qty, b.OtherPrice,
c.Customer, c.Address, c.Date, c.ChassyNo,
c.VehicleNo FROM wshpTopicIndex a
LEFT JOIN wshpEstimateDetails b ON (a.EstimateID = b.EstimateID)
INNER JOIN wshpEstimateIndex c ON (b.EstimateID = c.EstimateID)
WHERE (b.RandomNo = a.TopicIndex) AND (a.EstimateID = 54)
GROUP BY a.L2Topic, Substring(b.Des, 1, 900), c.Customer, a.TopicIndex, a.L1Topic, b.Qty,
b.PriceGroup, b.Price, b.L1TopicIndex, b.OtherPrice, c.Address, c.Date, c.ChassyNo, c.VehicleNo
ORDER BY b.PriceGroup, b.L1TopicIndex
//===============================================================
Can some one please help me to get rid of this problem??
Thank you,
Harshi"
View 1 Replies
View Related
Mar 5, 2008
I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:
CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville'
CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'
I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.
View 10 Replies
View Related
Jul 30, 2007
Hi All,
I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.
Thanks
View 7 Replies
View Related
Apr 14, 2006
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
View 5 Replies
View Related
Aug 30, 2007
Substring ('(' + left(@phone,3) + ')') + substring(@phone,4,3) + '-' + substring(@phone,7,4) + 'x' + right(@phone,4) getting an error on this code help please.
View 2 Replies
View Related
Oct 18, 2007
Hi,
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
Anyone have an idea why?
Mathieu Desbiens
View 1 Replies
View Related
Oct 30, 2007
Hi All,
Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters? Something like this:
Code Block
Select SUBSTRING(Select * from DB.dbo.Function('Parameter'), PATINDEX('%\%', Select * from DB.dbo.Function('Parameter'), 8)
Mannu.
View 1 Replies
View Related
Nov 12, 2004
Does anyone know what the above error message means?
View 4 Replies
View Related
Mar 3, 2014
I have a sql job which will execute the below stored proc.
Basically it Refresh the data by truncating the tables and using BCP to import the data. Job was running fine but today got a error message.
Invalid length parameter passed to the substring function. [SQLSTATE 42000] (Error 536). The step failed.
USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Refresh_tables]
[Code] ......
View 2 Replies
View Related
May 23, 2007
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
View 7 Replies
View Related
Feb 1, 2008
Hi,
I was trying to execute the following query.
select substring(ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''),1,charindex(',',ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''))-1) from tbl_lrf_company_details_with_codes
but i am getting the error as "Invalid length parameter passed to the SUBSTRING function."
Please advice
Thanks In advance
View 4 Replies
View Related
May 27, 2008
SELECT
C.Description,
SUBSTRING (
(CONVERT (VARCHAR (255), D.CurrentXmlValue)), --expression
(CHARINDEX ('>', CONVERT (VARCHAR (255), D.CurrentXmlValue)) + 1), --start
( (LEN (CONVERT (VARCHAR (255), D.CurrentXmlValue))) -
(CHARINDEX ('>', CONVERT (VARCHAR (255), D.CurrentXmlValue))*2) - 1
)--length
) AS Version
FROM
:
:
WHERE
:
:
This does not work while
:
:
--SUBSTRING (
(CONVERT (VARCHAR (255), D.CurrentXmlValue)) as expression, --expression
(CHARINDEX ('>', CONVERT (VARCHAR (255), D.CurrentXmlValue)) + 1) as start, --start
( (LEN (CONVERT (VARCHAR (255), D.CurrentXmlValue))) -
(CHARINDEX ('>', CONVERT (VARCHAR (255), D.CurrentXmlValue))*2) - 1
) as length --length
--) AS Version
:
:
works!!
I get
Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
Error.
Any idea why i am getting this??
I am trying to get rid of xml tags in the column.
Thanks,
View 4 Replies
View Related
Oct 9, 2006
Hi,
I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error,
€œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ
Through research I have no clue as what I need to do, or where to look.
The package runs without error when I execute the package itself.
Any help is greatly appreciated.
Thanks,
Lori
View 1 Replies
View Related
Aug 5, 2013
SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
View 4 Replies
View Related
Nov 28, 2005
My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.
SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+)
AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)
View 2 Replies
View Related
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
View 3 Replies
View Related
May 21, 2008
what is the error in that function, please help to find out
create function [dbo].[ufn_IsLeapYear] ( @pDate DATETIME) returns bit
as
begin
declare @b bit
if (YEAR(@pDate)%4=0 ) AND YEAR(@pDate)%100 != 0))
set @b = 1
else if (YEAR(@pDate) % 400 = 0)
set @b= 0
Return(@b)
end
spatle
View 1 Replies
View Related
Jun 8, 2014
I have the following SQL:
SELECT
MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (Select Name from kua where ID=A2.Valint) END) as Manage
FROM ree A1 inner join data A2 on A1.DataID=A2.ID
AND
A1.SubType=31066
inner join ancestors AN ON AN.DataID = A1.DataID and AN.AncestorID=9735190
Every time I run it i get this error:Cannot perform an aggregate function on an expression containing an aggregate or a subquery...If I change the Field definition to:
MAX(Case WHEN A2.AttrID = '23' AND A2.DefID = '9719132' THEN (A2.Valint) END) as Manager
then it works. The query Select Name from kua where ID=A2.Valint has only one field as result. Why do I get an error?
View 3 Replies
View Related
Jul 23, 2007
dear folks,
please check my function i'm getting error like this
Select statements included within a function cannot return data to a client.
create function getuomid(@uomcode varchar(50))
returns varchar
as begin
declare @uom_id varchar(50)
select uom_id from vuom where uom_code=@uomcode
return @uom_id
end
go
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 5 Replies
View Related
Jul 24, 2007
Dear experts,
please tell me where is the error in my code?
create function getitemid(@uomid varchar(50))
returns table
as begin
declare @itemid varchar(50)
select @itemid= column01 from table21 where column03=(select dbo.getuomid('no of leaves'))
return (@itemid)
end
go
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 8 Replies
View Related
Feb 1, 2008
Hi,
I am write one storedprocedure,inside that i use sum function like
SUM((CASE WHEN d_end_name_qualifier LIKE 'Oc' THEN duration ELSE 0 END)) d_to_stream
then i get following error,
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Dnyaneshwar Bhamare
View 5 Replies
View Related
Mar 17, 2008
With this statement I get an error where "Column QuoteHeaderID does not belong to table. " because Object reference isn't being set to an instance of an object, yet if I remove the max part it functions correctly.
Code Snippet
string num1 = "";
string sqlString = "SELECT MAX(QuoteHeaderID) FROM QuoteHeader";
try
{
cmd = new OleDbCommand(sqlString, connectCmd);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
oleDbDataAdapter = new OleDbDataAdapter(cmd);
oleDbDataAdapter.Fill(dt);
if (dt.Rows.Count > 0)
{
num1 = dt.Rows[0]["QuoteHeaderID"].ToString();
}
}
catch(Exception ex)
{
string s = ex.Message;
DisposeResources(ref oleDbDataAdapter, ref ds, ref connectFill, ref connectCmd, ref cmd);
cmd.Connection.Close();
return 0;
}
View 7 Replies
View Related
Oct 17, 2007
This is my function:
=sum(Iif(Fields!FlagPresenca.Value = 1,1,0)=Fields!DataTexto.Value)
I want the sum the values = 1 inside =Fields!DataText.Value, This function return me a error#, Can you help me? Thank you a lot!
View 5 Replies
View Related
Jun 28, 2004
What's wrong with this statement?
Dim drTemp As dsContacts.ContactsRow = dsContacts.Contacts.FindByContactID(iContactCode)
.
.
""If Not IsDBNull(drTemp.MobilePhone) Then Me.lblMobilePhone.Text = cstr(drTemp.MobilePhone)""
.
.
When I Execute it , it returns this error message :
Cast from type 'DBNull' to type 'String' is not valid
View 1 Replies
View Related
Sep 6, 2005
Hi,
When I try to create a CLR function in SQL 2005 (June CTP) I get the following error:
Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'.
The assembly registers successfully, with no errors.
I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO
The function's code is the following:
using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient;
namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) {
//Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return "";
//Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . }
return cFinal;
} }}
Thanks in advance,
Anderson
View 1 Replies
View Related
May 2, 2005
I have the below function which errors out telling me "Invalid use of 'getdate' within a function." I can run it as sql but not as a function, is there an issue with using getdate() in a function?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER function fnGetQuantity( @orderid int )
returns int
as
/************************************************** ****************************
** File: fnGetQuantity.sql
** Name: fnGetQuantity
** Desc: Used to calculate the total order quantity for an order
** Quantity is sigfrequency.timesperday * sigdosageamt.descr * orders.duration
**
** Return values: Quantity
**
** Called by:
**
** Parameters: order Id
** Input Output
** ---------- -----------
** @patientid
** Auth: DHoefgen
** Date: 04/30/05
************************************************** *****************************
** Change History
************************************************** *****************************
** Date: Author: Description:
** -------- -------- ------------------------------------------
** 05/02/05 KKowert Changed sql for effdt and added duration and
** times per day logic for zeros.
************************************************** *****************************/
begin
declare @QuantityTotal int
SELECT @QuantityTotal = (o.Duration * f.TimesPerDay * d.Descr)
FROM Orders o INNER JOIN
SIGFrequency f ON o.FreqID = f.FreqID INNER JOIN
SIGDosageAmt d ON o.DosageAmtID = d.DosageAmtID
WHERE (o.OrderID = @orderid) AND (f.Effdt =
(SELECT MAX(f2.Effdt)
FROM SIGFrequency f2
WHERE f2.FreqID = f.FreqID AND f2.Effdt <= getdate())) AND (d.Effdt =
(SELECT MAX(d2.Effdt)
FROM SIGDosageAmt d2
WHERE d2.DosageAmtID = d.DosageAmtID AND d2.Effdt <= getdate()))
return @QuantityTotal
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 2 Replies
View Related
