Remove the Space and Special Character from the each first word in string of column

Question

Hi,

I have a columnA in Table T1 with Job Title.

Having values

"Computer Programmer"

"   Database Analyst"

"-*%Data Architect"

"   #Team LEad"

I have to get rid of all the spaces or Special characters that is in front of each firast word of title as below:

"Computer Programmer"

"Database Analyst"

"Data Architect"

"Team LEad"

How can write the script for this. Do i have to write function for this. Any help is appreciated.

Thanks,

Punia

Answer 1

create table test (col   varchar(20));
insert into test values ('Computer Programmer'),('  Database Analyst'),('"-*%Data Architect'),('   #Team LEad')

select col,  substring(col,  patindex('%[a-zA-Z]%',col),len(col))  as newCol
from test

drop table test

Answer 2

Hi,

Jingyang Li 's code sample will also work,have a look at this link too - http://blog.sqlauthority.com/2008/10/10/sql-server-2008-enhenced-trim-function-remove-trailing-spaces-leading-spaces-white-space-tabs-carriage-returns-line-feeds/

---

sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

Answer 3

declare @tab table (col   varchar(20))
insert into @tab 
select 'Computer Programmer' union all
select '   Database Analyst' union all
select '-*%Data Architect' union all
select '   #Team LEad'

select stuff(col, 1, patindex('%[a-zA-Z]%',col)-1, '') from	@tab

---

Sandeep Mittal | My Blog - IT Developer Zone