Parsing a Name field - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Parsing a Name field

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-15 : 18:27:37

Hey Guys, I am new to SQL Team.com. My current problem is, I have written code to parse a name field, but it is not working. I have highlighted the line of code that is causing the error.

Please let me know if you see anything that looks odd.

Thank you,
Trudye

Error: Msg 174, Level 15, State 1, Line 6
The len function requires 1 argument(s).

SELECT
LTRIM(RTRIM(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))), CHARINDEX(' ', LTRIM(RTRIM(SUBSTRING(Details, 1, 26)))) - 1))) AS LastName,
SUBSTRING(Details, 1, 26),
LEN(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))), CHARINDEX(' ',
SUBSTRING(Details, 1, 26)))) + 1, CHARINDEX(' ', SUBSTRING(SUBSTRING(Details, 1, 26),
LEN(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))),
CHARINDEX('F', SUBSTRING(Details, 1, 26))) + 1, 26))))) AS FirstName
FROM dbo.tblImportAppliedBankDaily
WHERE TransactionID = 'A' and RecordID = 'C1'

The field looks like:
Brown JohnF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-15 : 18:32:30

SELECT dbo.fnParseString(-1, ' ', Details) AS LastName,
dbo.fnParseString(-2, ' ', Details) AS FirstName,
Details
FROM dbo.tblImportAppliedBankDaily
WHERE TransactionID = 'A' and RecordID = 'C1'

function found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 01:18:27

quote:
Originally posted by Trudye

Hey Guys, I am new to SQL Team.com. My current problem is, I have written code to parse a name field, but it is not working. I have highlighted the line of code that is causing the error.

Please let me know if you see anything that looks odd.

Thank you,
Trudye

Error: Msg 174, Level 15, State 1, Line 6
The len function requires 1 argument(s).

SELECT
LTRIM(RTRIM(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))), CHARINDEX(' ', LTRIM(RTRIM(SUBSTRING(Details, 1, 26)))) - 1))) AS LastName,
SUBSTRING(Details, 1, 26),
LEN(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))), CHARINDEX(' ',
SUBSTRING(Details, 1, 26)))) + 1, CHARINDEX(' ', SUBSTRING(SUBSTRING(Details, 1, 26),
LEN(LEFT(LTRIM(RTRIM(SUBSTRING(Details, 1, 26))),
CHARINDEX('F', SUBSTRING(Details, 1, 26))) + 1, 26))))) AS FirstName
FROM dbo.tblImportAppliedBankDaily
WHERE TransactionID = 'A' and RecordID = 'C1'

The field looks like:
Brown JohnF

You have left out one parameter of LEFT() function, you need to specify a length parameter.Thats what it is complaining about.Can i ask what is your expected o/p?

Trudye
Posting Yak Master

169 Posts

Posted - 2008-02-16 : 09:14:49

Hi Guys thanks so much for responding, I should have been more explcit. I should have displayed 2 rows of data. My expected output (o/p) is the place the last name in one field and the first name in another field. The data I displayed is 3 fields (Lname, fname, format_type). The field is 26 bytes the 'F' is the last position is not the middle name it is the format_type).

example:
Brown.................JohnF
Gibson.............charlieF
Lamb..................MaryF
Where the '.' represent spaces.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 12:31:32

Can you try like this:-

SELECT LEFT(Details,CHARINDEX(' ',Details)-1) AS firstname,
LEFT(REVERSE(LEFT(REVERSE(Details),CHARINDEX(' ',REVERSE(Details))-1)),LEN(REVERSE(LEFT(REVERSE(Details),CHARINDEX(' ',REVERSE(Details))-1)))-1) AS lastname
FROM dbo.tblImportAppliedBankDaily

Assumption is you always have first & last name with format specifer in the end. else you have to replace CHARINDEX(' ',Details) with

CASE WHEN CHARINDEX(' ',Details) >0 THEN CHARINDEX(' ',Details) ELSE LEN(Details) END

and
CHARINDEX(' ',REVERSE(Details)) with

CASE WHEN CHARINDEX(' ',REVERSE(Details))>0 THEN CHARINDEX(' ',REVERSE(Details)) ELSE LEN(REVERSE(Details)) END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources