Trimming Strings 1

[MarkEmerson]

I have a field that contains forenames. If a person has more than one forename they are listed in this field seperated by spaces.

I want to seperate the first forename from the rest of the field. How can I do this?

Basically i want everything up to the first space.

Thanks again.

Mark

 

[WZUP]

Try.

dim x

x=inst(1,namefield," &quot
firstname=mid(namefield,1,x)

hope this helps

 

[SQLBill]

Try this:

SELECT SUBSTRING(forename, 1, (patindex('% %', forename)))


PATINDEX returns an integer for the 'place' where the character/string being searched for begins.
IE> patindex('% %', 'abcde 12345') returns 6

SUBSTRING returns everything from forename beginning from position 1 and ending at the position returned by PATINDEX.

Refer to the BOOKS ONLINE.

-SQLBill

 

[SQLBill]

WZUP,

This is the Microsoft SQL Server forum. There isn't any INST, MID, or DIM command in MS SQL Server (which uses Transact SQL).

-SQLBill

 

[WZUP]

sorry about that, click the wrong forum.

 

[MarkEmerson]

Thanks Bill,

Thought the first soloution looked a bit "VBish"

 

[MarkEmerson]

I have discoverd a problem. If the name does not have a space in it then the name will be deleted. How do i get round this?

Heres my code:

UPDATE TMP_STAFF_UPDATE

SET Forename = SUBSTRING(forename, 1, (patindex('% %', forename)))

FROM TMP_STAFF_ADD, TMP_STAFF_UPDATE
WHERE TMP_STAFF_ADD.UserID = TMP_STAFF_UPDATE.UserID

 

[MarkEmerson]

Sussed it,

just added &quot; AND (patindex('% %', forename) <> 0)&quot; to the end of the WHERE clause.