asked on

removing numbers from a string

Hi, i have a string value and i want to remove the numbers from it.
The numbers could appear either before or after teh name it varies.
I have tried the following and some other queries but i am getting either NULL or Invalid length parameter passed to the substring function and i can not understand why, Can anyone please advise:

Ideally i would like to get rid of any brackets or numbers or values that are not realated to the actual name in the entry.

Thanks,
Putoch

Create table account(account_name varchar(20))
Insert into account values('123456 Pat Reilly')
Insert into account values('Mary Reilly 544687')
Insert into account values('s12658 Thomas Dunn')
Insert into account values('Liam Conrow  (s12658) ')
 
SELECT SUBSTRING(account_name, 1, Patindex('%[0-9][0-9][0-9]-%',account_name)-3) 
		+SUBSTRING (account_name, Patindex('%[0-9][0-9][0-9]-%',account_name)+9,30)
FRom account

Open in new window

Xyptilon2

How about (in pseudo query)

select trim(replace("1234567890()","", account_name) as account_name

Now of course this doesnt work since replace doesn't except a regex, but you get the idea, Perform a replace in memory, then trim the output to get rid of trailing / leading white space and you have the account name.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Putoch

ASKER

Excellent that is great, i didn't think of using a function for this thank you Sharath

Putoch

ASKER

thank you Xyptilon2 also for your advice.

Sharath S

glad to help you.