Error while parsing name field

[gertNo1]

Hey Guys, I have a large field (named Details) that contains names and other information. I am trying to parse the first and last names.

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

The data is:
THOMAS MICHAEL TF

The “F” in the last byte is a new field

Errors:
Conversion failed when converting the varchar value 'THOMAS MICHAEL T' to data type int.
Incorrect syntax near the keyword 'FROM'.

 

[SQLDenis]

missing comma after LastName also missing a close parenthese in the FirstName part

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

LEN(LEFT(SUBSTRING(Details, 1, 26), (CHARINDEX('F',
SUBSTRING(Details, 1, 26))) + 1)

you have the len as the first_name

Hopefully you are just trying to parse this nonsense to get it out of this format. You aren't really storing your infomation like this are you? If you are you need to revisit your database design.

"NOTHING is more important in a database than integrity." ESquared

 

[PatriciaObreja]

This part prodoces the error:
SUBSTRING(Details, 1, 26) - 1

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[gmmastros]

It looks to me like this may be fixed width data, so, perhaps this....

select SUBSTRING(Details, 1, 16) As LastName,
       SubString(Details, 17, 9) As FirstName,
       SubString(Details, 26, 1) As MiddleInitial
FROM   dbo.tblImportAppliedBankDaily
WHERE  TransactionID = 'A' and RecordID = 'C1'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

Try this query:

SELECT
LEFT(Details, CHARINDEX(' ', Details)) AS FirstName,
LTRIM(SUBSTRING(Details, CHARINDEX(' ', Details),
CHARINDEX('F', Details) -
CHARINDEX(' ', Details))) AS LastName
FROM tblImportAppliedBankDaily
WHERE TransactionID = 'A' and RecordID = 'C1'

It will return:
First Name: "THOMAS"
Last Name: "MICHAEL T"

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[PatriciaObreja]

And this query:

SELECT
LEFT(Details, CHARINDEX(' ', Details)) AS FirstName,

LEFT(LTRIM(SUBSTRING(Details, CHARINDEX(' ', Details),
CHARINDEX('F', Details) -
CHARINDEX(' ', Details))),
CHARINDEX(' ', LTRIM(SUBSTRING(Details, CHARINDEX(' ', Details),
CHARINDEX('F', Details) -
CHARINDEX(' ', Details)))))
FROM tblImportAppliedBankDaily
WHERE TransactionID = 'A' and RecordID = 'C1'

will return:
First Name: "THOMAS"
Last Name: "MICHAEL"

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium