Selecting Chars and Trimming 1

[abienz]

We have a table of names in the format 'John Smith' etc
We need to be able to trim this to the first 6 characters, not including spaces and convert to lowercase if possible.

I've got the 1st 6 characters OK but wondered if there's a way to get rid of the spaces in the middle? e.g.

select left ([fullname],6) for 'John Smith' gives 'John S' when I need 'johnsm'

Any ideas?

Thanks

 

[pankajv]

Use the replace function to remove all the spaces and then use the left function to get the first 6 characters.

REPLACE(@var,' ','') -- Replaces all the occurences of a space with ''

 

[abienz]

Ahh of course I was expecting a function like TRIM in VBScript but REPLACE is the obvious answer, thanks, a real case of not beeing able to see the wood for the trees