remove chars

I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D.
now I would like to set the firstname field with JOHN only and and set the middleinit field like D only
thanks

u can use charindex function to find out if there is any space exist in the value .

for example

select charindex(' ','JOHN D') if it returns a value greter than 0 then
u can seperate the feilds using substring function

ex


select substring('JOHN D',1,charindex(' ','JOHN D')-1)




------------
thanh nguyen at 3/13/01 12:18:16 PM

I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D.
now I would like to set the firstname field with JOHN only and and set the middleinit field like D only
thanks

Thanh,

The following script will give you any variable after the firstname (FYI)

SELECT substring(firstname ,(CHARINDEX (' ',firstname ) +1), len(firstname ))
FROM TABLE1


The following script will remove the middle initial from firstname column
UPDATE TABLE1
Set firstname = (substring(firstname ,1, (CHARINDEX (' ',firstname ) +1)))




------------
thanh nguyen at 3/13/01 12:18:16 PM

I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D.
now I would like to set the firstname field with JOHN only and and set the middleinit field like D only
thanks