Question regarding LEN 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 2000 Forums

Transact-SQL (2000)

Question regarding LEN function

Author

Topic

mirirom
Starting Member

7 Posts

Posted - 2008-08-27 : 11:30:25

Hi,

I've been writing a series of text parsing queries as part of a data scrubbing effort. Many of these require using the index location of either patterns or fixed strings in an equation that uses the LEN function.

For example:


UPDATE #CustomerContact
SET LastName = LEFT(ContactName, CHARINDEX(',', ContactName) - 1),
FirstName = RIGHT(ContactName, LEN(ContactName) - CHARINDEX(',', ContactName) - 1)

This is one example where I'll typically see an error returned when the query is run

"Invalid length parameter passed to the substring function."

Even by adding a conditional such as


FROM #CustomerContact 
WHERE LEN(ContactName) > 0 
AND CHARINDEX(',', contactName) > 0

doesn't seem to help. I was thinking that selecting only these rows would prevent checks on NULL and empty string length in the field being parsed, but apparently not. Is there an order in which the rows are being selected before work is performed in the UPDATE statement?

Really stumped by this. Any help/advice is appreciated.

- B

..::mirirom::..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 11:37:47

use CASE statement to check if the ContactName contains the ','.
Something like this

UPDATE 	#CustomerContact
SET 	LastName  = CASE WHEN CHARINDEX(',', ContactName) <> 0
			 THEN LEFT(ContactName, CHARINDEX(',', ContactName) - 1)
			 ELSE ContactName
			 END,
	FirstName = CASE WHEN CHARINDEX(',', ContactName) <> 0
			 THEN RIGHT(ContactName, LEN(ContactName) - CHARINDEX(',', ContactName) - 1)
			 ELSE ''
			 END

KH
[spoiler]Time is always against us[/spoiler]

mirirom
Starting Member

7 Posts

Posted - 2008-08-27 : 11:47:56

Hi KH,

Thanks for the reply. Unfortunately, your solution returns the same error

Invalid length parameter passed to the substring function.

In the past, I'd been able to get around this error by only applying the updates to rows were LEN() would return legitimate values (i.e. by first limiting the returned rowset), but as I pointed out in my post above, even this isn't working.

Thx again,

- B

..::mirirom::..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 11:52:02

please post the query you used and sample data

KH
[spoiler]Time is always against us[/spoiler]

mirirom
Starting Member

7 Posts

Posted - 2008-08-27 : 11:58:11

[code]
UPDATE #CustomerContact
SET LastName = LEFT(ContactName, CHARINDEX(',', ContactName) - 1),
FirstName = RIGHT(ContactName, LEN(ContactName) - CHARINDEX(',', ContactName) - 1)
FROM #CustomerContact
WHERE ContactName IS NOT NULL,
AND LEN(ContactName) <> 0
AND CHARINDEX(',', contactName) <> 0

100 rows of sample data
contactName
----------------------------------------
Alison Colwell
Martina Komor
Annette Sasse
Amanda McIlwrath
NULL
Davin Miller
Ghosh
NULL
NULL
pierce johnson
Marini
ancheng
NULL
Robert Lewis
NULL
Chris Borell
Reinhard Breitling
Harald
Cynthia Sides
NULL
NULL
Davin Miller
Annette Sasse
NULL
NULL
NULL
Tanaka, Miyabi
Eeva Brockmann
NULL
Robert A. Bouchard
Michal Shoresh
William Keener
Haihong
NULL
Renata Pellegrino
Renata Pellegrino
NULL
Pin Wang
Michael Chen
Charles Lutz
NULL
Michelle
Wanru Tseng
NULL
Ian Clarke
NULL
Annette Sasse
Liu, Jane
NULL
Jim Haber
NULL
Yossi Weizmann
Burton Wice
Andy
NULL
Renata Pellegrino
Cafer Eroglu
Stephanie Guzik
Jim Spearow
NULL
NULL
NULL
NULL
NULL
James E. Haber
Annette Sasse
Kaushiki Menon
Renata Pellegrino
NULL
NULL
NULL
NULL
NULL
Rosangela Frita
NULL
NULL
NULL
Her Aguilar
Mark Engleka
Wanru Tseng
Xiaoyu Li
Edwin ten Dam
NULL
Laura
Leo Formenoy
NULL
NULL
NULL
NULL
Liu, Jane
NULL
NULL
NULL
Aaron D DenDekker
NULL
NULL
Kendra Avery
Michal Ronen
NULL
NULL
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 12:11:01

tried, these sets of data is fine. No error

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 12:14:18

[code]DECLARE @Sample TABLE
(
contactName VARCHAR(100),
firstName VARCHAR(100),
lastName VARCHAR(100)
)

INSERT @Sample
(
contactName
)
SELECT 'Alison Colwell' UNION ALL
SELECT 'Martina Komor' UNION ALL
SELECT 'Annette Sasse' UNION ALL
SELECT 'Amanda McIlwrath' UNION ALL
SELECT NULL UNION ALL
SELECT 'Davin Miller' UNION ALL
SELECT 'Ghosh' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'pierce johnson' UNION ALL
SELECT 'Marini' UNION ALL
SELECT 'ancheng' UNION ALL
SELECT NULL UNION ALL
SELECT 'Robert Lewis' UNION ALL
SELECT NULL UNION ALL
SELECT 'Chris Borell' UNION ALL
SELECT 'Reinhard Breitling' UNION ALL
SELECT 'Harald' UNION ALL
SELECT 'Cynthia Sides' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Davin Miller' UNION ALL
SELECT 'Annette Sasse' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Tanaka, Miyabi' UNION ALL
SELECT 'Eeva Brockmann' UNION ALL
SELECT NULL UNION ALL
SELECT 'Robert A. Bouchard' UNION ALL
SELECT 'Michal Shoresh' UNION ALL
SELECT 'William Keener' UNION ALL
SELECT 'Haihong' UNION ALL
SELECT NULL UNION ALL
SELECT 'Renata Pellegrino' UNION ALL
SELECT 'Renata Pellegrino' UNION ALL
SELECT NULL UNION ALL
SELECT 'Pin Wang' UNION ALL
SELECT 'Michael Chen' UNION ALL
SELECT 'Charles Lutz' UNION ALL
SELECT NULL UNION ALL
SELECT 'Michelle' UNION ALL
SELECT 'Wanru Tseng' UNION ALL
SELECT NULL UNION ALL
SELECT 'Ian Clarke' UNION ALL
SELECT NULL UNION ALL
SELECT 'Annette Sasse' UNION ALL
SELECT 'Liu, Jane' UNION ALL
SELECT NULL UNION ALL
SELECT 'Jim Haber' UNION ALL
SELECT NULL UNION ALL
SELECT 'Yossi Weizmann' UNION ALL
SELECT 'Burton Wice' UNION ALL
SELECT 'Andy' UNION ALL
SELECT NULL UNION ALL
SELECT 'Renata Pellegrino' UNION ALL
SELECT 'Cafer Eroglu' UNION ALL
SELECT 'Stephanie Guzik' UNION ALL
SELECT 'Jim Spearow' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'James E. Haber' UNION ALL
SELECT 'Annette Sasse' UNION ALL
SELECT 'Kaushiki Menon' UNION ALL
SELECT 'Renata Pellegrino' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Rosangela Frita' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Her Aguilar' UNION ALL
SELECT 'Mark Engleka' UNION ALL
SELECT 'Wanru Tseng' UNION ALL
SELECT 'Xiaoyu Li' UNION ALL
SELECT 'Edwin ten Dam' UNION ALL
SELECT NULL UNION ALL
SELECT 'Laura' UNION ALL
SELECT 'Leo Formenoy' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Liu, Jane' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Aaron D DenDekker' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'Kendra Avery' UNION ALL
SELECT 'Michal Ronen' UNION ALL
SELECT NULL UNION ALL
SELECT NULL

UPDATE @Sample
SET firstName = LEFT(contactName, NULLIF(CHARINDEX(' ', contactName) - 1, -1)),
lastName = SUBSTRING(contactName, NULLIF(CHARINDEX(' ', contactName) + 1, -1), LEN(contactName))

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources