Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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.

Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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...


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 Replies !   View Related
SQL 2000 - Substring Function
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 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.
Thanks
Al

View Replies !   View Related
Substring Function Error.....
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 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,
Harshi"

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



Mannu.

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


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 Replies !   View Related
Invalid Length Parameter Passed To The SUBSTRING Function.
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 Replies !   View Related
Sql 2005 ... Invalid Length Parameter Passed To The SUBSTRING Function.
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 Replies !   View Related
Error: Invalid Length Parameter Passed To The SUBSTRING Function.
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 Replies !   View Related
Error In Query; &"Invalid Length Parameter Passed To The Substring Function&"
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 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:

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 Replies !   View Related
Syntax On A Join And RIGHT Function
Hi,

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?

thanks!

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)
union
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
Hey,

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:

---

SELECT
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

FROM
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

GROUP BY
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
Hi,

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

DOW     NOR
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:
A)
select word, sentence, token
from Words,
Sentences,
dbo.Split(sentence, ' ') -- implicitly joins Split result with Sentences?
where word = token

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

B)
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
)
RETURNS
@thistable TABLE
(
Color varchar(20)

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

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
FROM MAIN
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
Hello,

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)

RETURNS @MyTable TABLE

(

NextValue int NOT NULL

)

AS

BEGIN

INSERT INTO @MyTable(NextValue)

VALUES (@StartValue + 1)

INSERT INTO @MyTable(NextValue)

VALUES (@StartValue + 2)

RETURN

END

GO

CREATE TABLE TheTable

(

StartValue int NOT NULL

)

GO

INSERT INTO TheTable(StartValue)

VALUES (10)

INSERT INTO TheTable(StartValue)

VALUES (20)

GO

SELECT *

FROM TheTable CROSS JOIN

MyFunction(TheTable.StartValue)



View Replies !   View Related
Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Hello

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
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 Replies !   View Related
Substring Help
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 Replies !   View Related
Substring
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 Replies !   View Related
Substring???
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 Replies !   View Related
Substring
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 Replies !   View Related
SubString
Need to do something like SUBSSTRING in Access, any ideas?

View Replies !   View Related
Substring
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 Replies !   View Related
Need Help On Substring
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 Replies !   View Related
Substring
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 Replies !   View Related
Substring
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 Replies !   View Related
Substring
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 Replies !   View Related
Help With Substring
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 Replies !   View Related
Substring
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 Replies !   View Related
Substring
can anyone help me with the following report.

Create procedure Rpt_StaffDevelopmentReport
(@cmb1 as varchar(100),
@cmb2 as varchar(100) with encryption as

Begin
--@cmb1 is the site name (s.sitename), @cmb2 is the staffname in the form surname,forename

--@cmb2 will need to be split based on the , within the string
--tables needed are staffDevelopment , staffMember and Site
end


Three Tables.

Site
Columns
Siteid, sitename, address1, address2

Staffmember
Staffed, surname, forename,sex, address1, address2, siteid

Staffdevelopment
Staffed, development, personal


View Replies !   View Related
Substring
i have a column that has "Full name" of an individual.

I want to create a new column with just the first name.

How do i populate this column with just the first name...ie it just takes the string until the space from the "full name" column.

View Replies !   View Related
Updating A Substring
 Hi I am trying to update a part of a date string using the following sql statement. Alas, I'm having no joy.cmd.CommandText = "UPDATE tb_bookings SET SUBSTRING(startDate,12,5) = @newStartTime  WHERE requestPackID = @reqPackID";   The date has been saved  in the db in the following format:Fri May 23 17:30:00 GMT+0900 2008 Any idea why this isn't working. Any help much appreciated.Barry 

View Replies !   View Related
Substring(very Very Urgent)
 hi frenz:
Can any one send me the code for
substring multiple cell values of same column and put it into one cell of that column.

View Replies !   View Related
T-SQL SUBSTRING For RequestDateTime
I tried to mimic some code I found but on RequestDateTimeSUBSTRING(RequestDateTime,(charindex(':',RequestDateTime)+1),len(RequestDateTime)) AS 'Date Downloaded'I can only use SUBSTRING on char type strings.How would I get maybe only up to the first : character in the RequestDateTime? 

View Replies !   View Related
Can You Use Substring In ASP.NET On A DropDownList ?
I'm using the GridView to display some accounting infromation. I have a project where I have a 14 character control number. I would like to have a dropdown list to select the account classification of records to be displayed. The accounting classification is the first two characters of the control number.  So the dropdown list needs to show unique first two characters and the GridView will be filtered on these two characters.  I have been trying to use "substring" in the ASP.NET code; not even sure you can.
Any suggestions on how to accomplish this would be greatly appreciated.  See code below:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="control_num" DataValueField="control_num">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:GPCRReportsConnectionString %>"
SelectCommand="SELECT DISTINCT [substring(control_num,1,2)] FROM [Request]"></asp:SqlDataSource>
ERROR: Invalid column name 'substring(control_num,1,2)'.

View Replies !   View Related
Substring Sql Statement
I have a SQL column that contains something like this:






ORLANDO        ,FL.    32803

COCOA       , FL. 32922-8617

SATELLITE BCH,FL  32937-3523

TAMPA       , FL. 33609-3105

EAU GALLIE      ,FL.   32935
I need only the name of the city, dont need FL or the zip code, how can I do a substring statement on sql to get only that?
Thanks,
Erick

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved