Parse a name field

Question

Post reply

Parse a name field

trudye10

SSCommitted

Points: 1721
More actions
February 15, 2008 at 4:41 pm

#203489

Hey Guys, I am new to SQL ServerCentral.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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Where does the 26 come from?

If all of your names to parse look like 'Brown JohnF' this will work

create table #tblImportAppliedBankDaily (Details varchar(40))

insert into #tblImportAppliedBankDaily (Details) select 'Brown JohnF'

insert into #tblImportAppliedBankDaily (Details) select 'Johnson AbleR'

select

substring(Details, 1, charindex(' ', Details)-1),

substring(Details, charindex(' ', Details)+1, len(Details)-charindex(' ', Details)-1),

right(Details, 1)

FROM #tblImportAppliedBankDaily

-- where whatever

trudye10 SSCommitted Points: 1721 More actions · Answer 2

Hi ksullivan, thanks so much for responding so quickly. I'm sorry I should have added some additional information. The name field is 26 bytes. The 'F' behind John Brown signifies a new field (I think its fieldname is FormatType).

So the data would be

Brown JohnF

Smith MarieF

Gibson CharlieF

Sorry about that,

Thanx again,

Trudye

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

The LEN failure is because you are missing a couple of right parenthesis in the code line.

Also, is there only 1 name per row or do they look like something else.

Also, is there only 2 parts per name ie, First Name and Last Name???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 4

Something else to think about...

What if your name is like: 'Smith, JeffF' ?

Anyway to get rid of the 'F' as a delimiter, perhaps use some other character?

If it was easy, everybody would be doing it!;)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

It's simple... if it ALWAYS there, just don't include the last character.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 6

...if there is no data after the 'F' on each row (we can only hope it is that easy).

If it was easy, everybody would be doing it!;)