Join Using A Substring Function

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.



Substring Function

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...


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


View Replies View Related

SQL 2000 - Substring Function


I want to select the characters before "-" from Column A.
For example:

I'd would like to select it as:

Please Help!!!!!!!!!!!!!!!!!!!!! :confused:

View Replies View Related

What Is The Max Length Of Substring Function.

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.

View Replies View Related

Substring Function Error.....

select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 0)
-------------------- ----------- ----------- -----------
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)

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.


View Replies View Related

Substring Function Can't Figure Out

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 Replies View Related

Problem In Substring Function

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,

View Replies View Related

Is There A Function Like SUBSTRING But For Integers?

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 Replies View Related

Substring Function Requires 3 Arguments

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 Replies View Related

Performance Issue Using Left Or Substring Function


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 Replies View Related

Substring And Patindex Having An User Defined Function

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)


View Replies View Related

SUBSTRING In User Defined Function - Invalid Column

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 Replies View Related

Invalied Length Parameter Passed The Substring Function

Does anyone know what the above error message means?

View Replies View Related

Invalid Length Parameter Passed To The SUBSTRING Function




(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


) AS Version


This does not work while



(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


I get

Msg 536, Level 16, State 5, Line 2

Invalid length parameter passed to the SUBSTRING function.
Any idea why i am getting this??
I am trying to get rid of xml tags in the column.


View Replies View Related

Invalid Length Parameter Passed To The SUBSTRING Function.


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 Replies View Related

Sql 2005 ... Invalid Length Parameter Passed To The SUBSTRING Function.

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.

View Replies View Related

Error: Invalid Length Parameter Passed To The SUBSTRING Function.


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
DECLARE @Teams varchar(2000)

truncate table ForEthnicPivot

INSERT INTO ForEthnicPivot
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' )

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

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?



View Replies View Related

Error In Query; &"Invalid Length Parameter Passed To The Substring Function&"

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 :)
VLF_InfectionDestination is nvarchar 254

SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
__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'))

View Replies View Related

Odd Error Upon SqlDataReader.Read() &"Invalid Length Parameter Passed To The Substring Function.&"

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:

orderReader = orderCommand.ExecuteReader()


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 Replies View Related

Syntax On A Join And RIGHT Function


Trying to join two tables together.

Select * from ...

inner join

on table1.ID = table2.ID;

this works fine, however i want to use the RIGHT function so that i get the last four digits of the ID. this causes a parse error:

inner join on table1.right([ID],4) = table2.right([ID],4);

searched a bit but couldn't find a way to do what im trying to accomplish. is there a proper syntax?


View Replies View Related

Call Function For Inner Join

I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {

select centerid from GetChildCenter(@Centerid)
select centerid from getParentCenter(@Centerid)
} as Centerc

on c.Centerid = client.Centerid

Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join@Centercon c.Centerid = client.Centerid

I just want to know which one is better performance wise..
because thereis millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View Replies View Related

Aggregate Function/Join Troubles


I am running into trouble performing a second join on a column while using aggregate functions over that table.

What I think is happening is that the aggregate values (sum, avg, count) are being multiplied because I am joining on the same table a second time, but I don't know how to fix or avoid this problem.

Here's the query:


CAST(ro.xing_tc_no AS INT) AS loc_id,
SUM(ro.total_fatal) AS tot_fatal,
SUM(ro.total_serious) AS tot_serious,
SUM(ro.total_minor) AS tot_minor,
COUNT(ro.report_no) AS tot_accidents,
AVG(ro.NO_OCCUPANTS) AS num_occupants

UserHotspots uh

LEFT JOIN rods_k_crossing AS rkc
ON rkc.location_id = uh.CrossingID

LEFT JOIN rods_occurrence AS ro
ON rkc.tc_location_nbr = ro.xing_tc_no



The second join is also performed on rods_k_crossing and that is when the sums, avgs, and counts get multiplied. If I take that second join out the numbers come out fine.

Thanks for any help!

View Replies View Related

Join With Table Valued Function


I want to join a table valued function but function parameter should left joined table's primary key .... this is posible in oracle by pipeline method ..
SELECT A.Col1,A.Col2,B.Col1,B.Col2
FROM Tab As A LEFT OUTER JOIN TblFunction(A.Pkey) B
ON A.Col1 = B.Col1

any body help me ... thanx in advance..

View Replies View Related

Join On Agragate Function Between Tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

3           8934
6           22397
7           23328
1           23401
4           1938
2           24399
5          1112


Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1   Amount 1     DOW2      Amount 2
6           332953802   6            332953802
3           42248886     3            42248886
1           330281714   1            330281714
7           335759904   7            335759904
4           1232568      4            1232568
5           210168        5            210168
2           366985359   2           366985359

Where the numbers are way off.

Any suggestions?

View Replies View Related

How To Join Using A Table-valued Function?

Hi there. I've hit some gap in my SQL fundementals. I'm playing with table-valued functions but I can't figure out how to join those results with another table. I found another way to hit my immediate need with a scalar function, but ultimately I'm going to need to use some approach like this. What am I misunderstanding here?

The Given Objects:
function Split(stringToSplit, delimiter) returns table (column: token)
table Words (column: Words.word) -- table of predefined words
table Sentences (column: Sentences.sentence) -- table of sentences; tokens may not be in Words table, etc

The Problems:
1) how do I query a set of Sentences and their Tokens? (using Split)
2) how do I join tables Sentences and Words using the Split function?

The Attempts:
select word, sentence, token
from Words,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

select word, sentence
from Words, Sentences
where word in (select token from dbo.Split(sentence, ' ')) -- correlated subquery?

resulting error: "'sentence' is not a recognized OPTIMIZER LOCK HINTS option."

View Replies View Related

Passing Variable To Table Function In Join

Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.

There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint

There is a WETPAINT table:
ItemNo int,
Color varchar(20)

There is a DRYPAINT table:
ItemNo int,
Color varchar(20)

Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType

So I created a table function called getTable:

CREATE FUNCTION [dbo].[gettable]
@ItemType int = 1
@thistable TABLE
Color varchar(20)

if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT

This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:

SELECT MAIN.ItemNo, a.Color
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo

Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.

Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.

PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.

View Replies View Related

Problem Performing A Join On A Function In A SQL Query


Can someone explain why this code contains the following error:

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "TheTable.StartValue" could not be bound.

CREATE FUNCTION MyFunction(@StartValue int)



NextValue int NOT NULL




INSERT INTO @MyTable(NextValue)

VALUES (@StartValue + 1)

INSERT INTO @MyTable(NextValue)

VALUES (@StartValue + 2)






StartValue int NOT NULL



INSERT INTO TheTable(StartValue)


INSERT INTO TheTable(StartValue)






View Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language


Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses

Thank You

View Replies View Related

Using Substring


Can anyone show me how to do the following:

I have this snippet of data


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 :

Thanks in advance

View Replies View Related

Substring Help

please help me in developing query to satisfy this...
i have table called test and the table data looks like this.


the query needs to return all rows data after . the result set should return this


appreciate your help..

View Replies View Related

Copyrights 2005-15, All rights reserved