Substring Function
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
ADVERTISEMENT
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
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
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
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
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
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
Jul 5, 2001
Hi all,
I have just started using SQL7 and quite dumb at it.
Here is my problem
i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.
As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet.
Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.
Any help would be greatly appreciated
Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way
select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0
Arun
View 2 Replies
View Related
Nov 5, 2001
Hey you sql programming guru's
I need help seperating a name from first name and last name
The field is like this,
last name, first name
example Doe, John
I need to seperate the last name from the , to the first character
and the last name from the , to the last character.
I think I have to use a substring but not sure how tell it to stop and
start when it gets to the comma.
Can someone please help me.
Thanks,
Dianne
View 1 Replies
View Related
Aug 8, 2001
Need to do something like SUBSSTRING in Access, any ideas?
View 1 Replies
View Related
Oct 4, 2004
Hi
Can anyone show me how to do the following:
I have this snippet of data
ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270
I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.
So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270
Thanks in advance
View 9 Replies
View Related
Feb 4, 2005
Hi,
please help me in developing query to satisfy this...
i have table called test and the table data looks like this.
col1
123.abc
1.ab
12.cba
the query needs to return all rows data after . the result set should return this
abc
ab
cba
appreciate your help..
thanks
sskris
View 1 Replies
View Related
Feb 3, 2004
I have a field that contaings 15 characters. I want to just pull the first 6. So anything that matches these 6 will be returned.
View 10 Replies
View Related
Apr 8, 2004
We have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.
ACustomerName
ACustomerNameCredit
ACustomerNameDebit
Thanks!
View 14 Replies
View Related
Jun 2, 2004
I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:
Coulumn1
-------------------------------
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210
I tryed this:
use cimpro1
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
from opcshto
For some reason, when I run the query I get this for State:
State
-------------------------
CA91733617
LA71172
CA90249107
WI53216
IL61944
IL62832
MN56283
ME04210
When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.
Any help is appreciated.
View 7 Replies
View Related
Sep 13, 2006
iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error
query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."
But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @
where did i go wrong
View 2 Replies
View Related
Dec 1, 2006
i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code
select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate
View 7 Replies
View Related
Mar 19, 2007
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks
View 9 Replies
View Related
Mar 19, 2007
Hi All
Iam new to sql, Iam using sql 2000 and have a field datatype varchar.
An example of the string: -
CN=Leighton Morgan/OU=WR/O=Extranet
I need to write a query that will allow me to extract the following:-
Name: Leighton Morgan
Company: WR
and lastly the word Extranet
Please can you help
Many Thanks
View 1 Replies
View Related