Problem with nulls going into a function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with nulls going into a function

Author

Topic

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-14 : 06:22:22

I'm creating a view with the code

SELECT     ID, decPrice, Application_Date, Postcode, HouseType, NewBuild, LeaseStatus, SAON, PAON, Thoroughfare, Locality, Town, District, County, Easting, Northing, 
                      strAddress, LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) AS PostOut
FROM         dbo.AllEWdata
WHERE     (Postcode IS NOT NULL) AND (Postcode <> '\N')

however, it keeps throwing an error
Invalid length parameter passed to the LEFT or SUBSTRING function
which I believe is because there are nulls in some of the Postcodes being fed into the LEFT function. Is there any way around this? Even if its just to ignore or filter records with null postcodes before the function is used?

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 06:39:31

Case when PostCode is not null then LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) AS PostOut else PostCode end as PostOut

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-14 : 08:11:11

Case when CHARINDEX(' ', Postcode)>0 then LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) else PostCode end as PostOut

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 06:00:20

if your intention is just to avoid spaces in postcode then you can just use LTRIM(RTRIM())

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-18 : 07:06:57

All I've been trying to do is create a view in which postcodes are trimmed down to only the left half of the postcode, i.e up until the space. Unfortunately whenever I query the view I get the error Invalid length parameter passed to the LEFT or SUBSTRING function which I can only assume is because there are null values getting passed into the view? I've tried the code you guys have posted but so far not been able to get it to work. :( Still get the same error

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-18 : 07:26:33

My most recent attempts to run the query are all resulting in out of memory errors. :S

ghanshyam007
Starting Member

6 Posts

Posted - 2011-01-18 : 08:09:09

Post some sample data with code.
thus we can check where exactly the error is coming

silver_surfer21
Starting Member

21 Posts

Posted - 2011-01-18 : 11:12:49

Ok, code is

SELECT     ID, decPrice, Application_Date, Postcode, HouseType, NewBuild, LeaseStatus, SAON, PAON, Thoroughfare, Locality, Town, District, County, Easting, Northing, 
                      strAddress, CASE WHEN Postcode IS NOT NULL THEN LEFT(Postcode, CHARINDEX(' ', Postcode) - 1) ELSE PostCode END AS PostOut
FROM         dbo.AllEWdata
WHERE     (Postcode IS NOT NULL) AND (Postcode <> '\N')

and some data would be

ID	decPrice	Application_Date	Postcode	HouseType	NewBuild	LeaseStatus	SAON	PAON	Thoroughfare	Locality	Town	District	County	Easting	Northing	strAddress	GUID
3403791	37000	2001-10-06	NULL	F	Y	L	DUMBARTON HOUSE COURT	27	CraigKennochie Terrace	NULL	Dumbarton	NULL	NULL	0	0	27 DUMBARTON HOUSE COURT BRYNYMOR ROAD,  SWANSEA,  ,	{A96E4D6F-96A8-4206-B981-89FEB6F167D8}

gbiddle
Starting Member

1 Post

Posted - 2011-04-27 : 08:50:50

I don't think that Null values are causing the problem. I believe that some of your postcodes must be incorrectly formed. If you run your query against valid postcodes (I am assuming British?) then the query runs fine. Errors occur when only the first part of the postcode is available (e.g. 'WR1'). You can combat this problem by adding an extra space on the end of each postcode. See amended query below:-

SELECT ID, decPrice, Application_Date, Postcode, HouseType, NewBuild, LeaseStatus, SAON, PAON, Thoroughfare, Locality, Town, District, County, Easting, Northing,
strAddress, LEFT(Postcode + ' ', CHARINDEX(' ', Postcode + ' ') - 1) AS PostOut
FROM dbo.AllEWdata
WHERE (Postcode IS NOT NULL) AND (Postcode <> '\N')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources