April 26, 2012 at 9:03 am
Hi there we have a database table and a field which is an address field.
The address field is a nvarchar at 255 in length.
This address field includes all of the address but seperated over lines by a carridge return.
What I want to pull back is just the first line of the address up to the first carridge return.
Is there a way to do this at all?
Thank you
April 26, 2012 at 9:10 am
Something like this, perhaps:
SELECT Addr,
CHARINDEX(CHAR(10), Addr, 1),
LEFT(Addr, CHARINDEX(CHAR(10), Addr, 1) - 1)
FROM ( VALUES ( '123 N Main St
City, ST Zip45') ) AS V (Addr)
I broke out each piece of the calculation in its own column, just to show it.
The From (Values) piece is SQL 2008, and you posted in SQL 2000, so you'll need to use a valid "From" clause for SQL 2000, I assume. But that should be easy enough for you to do. What matters here is the Left() piece of it, which is SQL 2000 compatible.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 26, 2012 at 9:24 am
Edited as misread post
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 26, 2012 at 9:41 am
Thanks. I tried your code but get the following error -
(2 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
April 26, 2012 at 9:44 am
Ryan Keast (4/26/2012)
Thanks. I tried your code but get the following error -(2 row(s) affected)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
Yes I edited my previous post as I misread what you were after, posted before I put my brain in gear!:crazy:
GSquared's solution looks like a winner 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 26, 2012 at 9:45 am
My code is attached
SELECT SALES.Reference AS 'SalesOrder Referance'
,SERV2.Reference AS 'ServiceOrder Reference'
,SALESTAT.NAME AS 'Sales Order Status'
,SALECAT.Category AS 'Sales Order Category'
,SOJ1.[No Of SOJ] AS 'NoOfSOJ'
,SOJ2.[JobNumber] AS 'SOJ Number'
,SUB1.[ActivityDate] AS 'Appointment Date'
,CUST1.ShortName AS 'ParentCustomer'
,SOJ2.JobStatusID
,PERS.ShortName
,CUST.Address
,CHARINDEX(CHAR(10), CUST.Address, 1)
,LEFT(CUST.Address, CHARINDEX(CHAR(10), CUST.Address, 1) - 1)
FROM dbo.SalesOrders
AS SALES
INNER JOIN
dbo.SalesOrdersStatus
AS SALESTAT
ONSALES.SOStatusID = SALESTAT.UID
LEFT OUTER JOIN
dbo.SalesOrdersCategories AS SALECAT
ONSALES.SalesOrderCategoryID = SALECAT.UID
LEFT OUTER JOIN
dbo.Customers AS CUST
ONSALES.CustomerID = CUST.UID
LEFT OUTER JOIN
dbo.Customers AS CUST1
ONCUST.ParentCustomerID = CUST1.UID
/* This Super Query is to work out those that have more than one service */
LEFT OUTER JOIN
(
SELECT SALES.Reference AS 'SalesRef',SALESSTATUS.Name AS 'SalesName',COUNT(SERV.Reference) AS 'Number Of Service Orders Attached'
FROM dbo.SalesOrders
AS SALES
INNER JOIN
dbo.SalesOrdersStatus
AS SALESSTATUS
ON
SALES.SOStatusID = SALESSTATUS.UID
INNER JOIN
dbo.ServiceOrders
AS SERV
ON SALES.UID = SERV.DocumentID
WHERE SALES.RecordStatusID = '0'
AND SALES.SOStatusID NOT IN ('5','7','11','13','19','17','16','18','21')
GROUP BY SALES.Reference ,SALESSTATUS.Name
HAVING COUNT(SERV.Reference) > '1'
) AS SERV1
ON SALES.Reference = SERV1.SalesRef
INNER JOIN
dbo.ServiceOrders AS SERV2
ONSALES.UID = SERV2.DocumentID
/* To get the amount of Service Order Jobs against A Service Order */
LEFT OUTER JOIN
(SELECT SOJ.ServiceOrderID
,COUNT(SOJ.JobNumber)AS 'No Of SOJ'
FROM dbo.ServiceOrdersJobs AS SOJ
WHERE SOJ.RecordStatusID = '0' --AND SOJ.ServiceOrderID = '150071'
GROUP BY SOJ.ServiceOrderID
)AS SOJ1
ON SERV2.UID = SOJ1.ServiceOrderID
/*To get the job number attached*/
LEFT OUTER JOIN
(
SELECT SOJ.ServiceOrderID
,SOJ.JobNumber
,SOJ.JobStatusID
,SOJ.AssignedToPersonnelID
FROM dbo.ServiceOrdersJobs AS SOJ
WHERE SOJ.RecordStatusID = '0'
) AS SOJ2
ON SERV2.UID = SOJ2.ServiceOrderID
/*JOIN TO SUBQUERY IN ORDER TO CALCULATE THE MOST RECENT APPOINTMENT STATUS */
LEFT OUTER JOIN
(SELECT
ACT.OwnerDocumentID
,MAX (APP.ActivityDate)AS ActivityDate
,MAX (APP.StartTime)AS ActivityStart
FROM dbo.Activity AS ACT
INNER JOIN
dbo.Appointments AS APP
ON
ACT.UID = APP.ActivityID
WHERE ACT.OwnerDocumentTypeID = '20'
AND
ACT.CreatedDate >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)
GROUP BY ACT.OwnerDocumentID
)AS SUB1
ON
SOJ2.JobNumber = SUB1.OwnerDocumentID
LEFT OUTER JOIN
dbo.Personnel AS PERS
ONSOJ2.AssignedToPersonnelID = PERS.UID
WHERE SALES.RecordStatusID = '0'
AND SALES.SOStatusID NOT IN ('5','7','11','13','19','17','16','18','21')
ANDSERV1.SalesRef IS NULL /* In order to only pull back sales orders with one service order*/
ANDSOJ1.[No Of SOJ] = '1'
AND SOJ2.JobStatusID NOT IN ('33','50')
AND SALECAT.Category <> 'Construction'
ANDSUB1.[ActivityDate] >= GETDATE()
The error I get is actually -
Invalid length parameter passed to the substring function.
I don't really understand the logic of what the following is doing
,CUST.Address
,CHARINDEX(CHAR(10), CUST.Address, 1)
,LEFT(CUST.Address, CHARINDEX(CHAR(10), CUST.Address, 1) - 1)
So not sure how to progress this one. Any ideas?
April 26, 2012 at 9:49 am
Gus's code didn't work for me in SQL Server 2005, this is what I ended up running:
SELECT Addr,
CHARINDEX(CHAR(10), Addr, 1),
LEFT(Addr, CHARINDEX(CHAR(10), Addr, 1) - 1)
FROM ( select '123 N Main St
City, ST Zip45' ) AS V (Addr)
Give this a try since it looks like you may be running SQL Server 2000. Just a guess as this is posted in a SQL Server 7,2000 forum.
April 26, 2012 at 9:53 am
If you reread Gus's post he broke out the Addr, CHARINDEX, and LEFT processes as a demonstration of each of the pieces.
April 26, 2012 at 9:56 am
Are you certain its a carrage return?
This works
SELECT Addr,
CHARINDEX(CHAR(10), Addr, 1),
LEFT(Addr, CHARINDEX(CHAR(10), Addr, 1) - 1)
FROM ( select '123 N Main St
City, ST Zip45' ) AS V (Addr)
This errors
SELECT Addr,
CHARINDEX(CHAR(10), Addr, 1),
LEFT(Addr, CHARINDEX(CHAR(10), Addr, 1) - 1)
FROM ( select '123 N Main St City, ST Zip45' ) AS V (Addr)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 26, 2012 at 9:57 am
If you are grtting this error:
The error I get is actually -
Invalid length parameter passed to the substring function.
It means some of your data does not contain the value CHAR(10).
April 26, 2012 at 10:54 am
To avoid the invalid length error, you'll need to wrap it in a Case statement that checks if the string contains a carriage-return.
case when Addr like '%' + char(10) + '%' then left(...) else Addr end
Something like that.
What my code does is find the position of the first char(10) (carriage-return) in a string, then use that to set the end value of the Left() function. Left() will cut a string at a certain point. CharIndex will find the position of one string inside another. They work together in this case to figure out where to cut it, and then to cut it.
But if there's no carriage-return, then CharIndex returns 0, and Left() can't accept a length of 0, so it gives an invalid length error. The case statement will test for that, and not run the Left() function if there's no char(10) in the string.
Helps?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2012 at 2:08 am
Sorry guys. Thanks for your help but I just don't understand enough about TSQL to appreciate what you are suggesting.
SELECT SALES.Reference AS 'SalesOrder Referance'
,SERV2.Reference AS 'ServiceOrder Reference'
,SALESTAT.NAME AS 'Sales Order Status'
,SALECAT.Category AS 'Sales Order Category'
,SOJ1.[No Of SOJ] AS 'NoOfSOJ'
,SOJ2.[JobNumber] AS 'SOJ Number'
,SUB1.[ActivityDate] AS 'Appointment Date'
,CUST1.ShortName AS 'ParentCustomer'
,SOJ2.JobStatusID
,PERS.ShortName
,CUST.Address
,CHARINDEX(CHAR(13), CUST.Address, 1)
,LEFT(CUST.Address, CHARINDEX(CHAR(13), CUST.Address, 1) - 1)
I'm not sure if it is a carridge return or not. I'm not sure how I would find that out? If I do the above for either CHAR (10) or CHAR (13) I get the following error -
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
I understand that this means one of my results in the query doesn't have a CHAR(13) or a CHAR(10) and so I therefore will have to use a CASE statement, however I still don't get whether I add it to after what I have in my SELECT statement above or I place it after.
Appreciate your help on this one. Unfortunatley your dealing with an idiot.
April 27, 2012 at 12:29 pm
SELECT SALES.Reference AS 'SalesOrder Referance'
,SERV2.Reference AS 'ServiceOrder Reference'
,SALESTAT.NAME AS 'Sales Order Status'
,SALECAT.Category AS 'Sales Order Category'
,SOJ1.[No Of SOJ] AS 'NoOfSOJ'
,SOJ2.[JobNumber] AS 'SOJ Number'
,SUB1.[ActivityDate] AS 'Appointment Date'
,CUST1.ShortName AS 'ParentCustomer'
,SOJ2.JobStatusID
,PERS.ShortName
,CASE WHEN CUST.Address LIKE '%' + CHAR(13) + '%' THEN LEFT(CUST.Address, CHARINDEX(CHAR(13), CUST.Address, 1) - 1) ELSE CUST.Address END AS Address
Like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 3, 2012 at 8:59 pm
Instead of CASE you may use also ISNULL(NULLIF) construction:
LEFT(CUST.Address, ISNULL( NULLIF(CHARINDEX(CHAR(13), CUST.Address, 1), 0) - 1), LEN(CUST.Address) ) )
_____________
Code for TallyGenerator
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply