Extracting certain characters from a string

[ashab02]

Hello

I have a set of data which contains address details. What I would like to do is get the first letter of the street name but the problem I have is that some of data contains records like
'Flat 4, Hope Lane'
'Flat 4 Hope Lane'

The code I have is as follows and it only handles the first space

select ltrim(substring('Flat 4 Hope Lane',charindex(' ', 'Flat 4 Hope Lane'),2))

This returns '4' but I want it to return 'H' as this is the first letter of the street name.

The above code works fine if we have data like '12 Hope Lane'

(I have hardcoded the value for illustration purposes)

Has anyone got any ideas on how I can do this??

Thanks

 

[IanWaterman]

Why don't you just do a replace on the string.

replace(stringfield, ',', '')

Then build your query around that.

Ian

 

[ashab02]

i'm not sure how the replcae will help me for the following example

'Flat 4 Hope Lane'

 

[IanWaterman]

It will not do anything.

YOu implied that 'Flat 4 Hope Lane' worked fine.

Ian