remove digits

Question

Post reply

remove digits

shashianireddy

SSC Eights!

Points: 914
More actions
April 3, 2014 at 5:40 am

#305913

i have table like
name
22rajuvar
45 vamsgui
87 praveen
67kumar
32 vamshi
above field mix with digits and charcter
i want to remove digits
i want o/p like ............
name
rajuvar
vamsgui
praveen
kumar
vamshi

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Something like this

Declare @RemoveNumeric as varchar(30) = '12112this is what i want'

Declare @n table ( number int )

insert into @n

Select top 10 ROW_NUMBER() Over ( ORder by object_id) - 1

from sys.all_columns

select @RemoveNumeric = replace(@RemoveNumeric, Cast(number as varchar(1)),'')

from @n

select @RemoveNumeric

kumariannapureddy SSC Journeyman Points: 89 More actions · Answer 2

CREATE TABLE Temp12(ProductName VARCHAR(20))

INSERT INTO Temp12 VALUES('123aaa'),('abcd'),('abc123'),('ab56def'),('xyz'),('9999'),('3946'),('9236'),('854')

USE DBTraining1

GO

IF OBJECT_ID('fn_GetAlphabets') Is Not Null

DROP FUNCTION dbo.fn_GetAlphabets

GO

CREATE FUNCTION fn_GetAlphabets(@input VARCHAR(20))

RETURNS VARCHAR(1000)

AS

BEGIN

WHILE PATINDEX('%[^a-z]%',@input) > 0

SET @input = STUFF(@input,PATINDEX('%[^a-z]%',@input),1,'')

RETURN @input

END

GO

SELECT * FROM Temp12

WHERE dbo.fn_GetAlphabets(Temp12.ProductName) = ProductName

drop function fn_GetAlphabets

rodjkidd SSCoach Points: 15883 More actions · Answer 3

Given that the OP's sample data includes spaces as well as numbers...

CREATE TABLE [dbo].[Names](

[Name] [varchar](200) NULL

) ON [PRIMARY]

INSERT INTO NAMES

VALUES ('22rajuvar'),

('45 vamsgui'),

('87 praveen'),

('67kumar'),

('32 vamshi')

SELECT REVERSE(LEFT(REVERSE(name), (PATINDEX('%[^a-z]%', REVERSE(NAME)))-1)) AS CleanedName

FROM Names

Gives the output;

rajuvar

vamsgui

praveen

kumar

vamshi

You will have to test against Case Sensitive collations if you use them. I did create a case sensitive version of the test table and [^a-z] still works, but I thought it wouldn't with an Upper Case letter at the start of a name.

Rodders...

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Simplifying Rodders code if digits will only appear at the beginning of the string.

CREATE TABLE #Names(

[Name] [varchar](200) NULL

)

INSERT INTO #NAMES

VALUES ('22rajuvar'),

('45 vamsgui'),

('87 praveen'),

('67kumar'),

('32 vamshi'),

('123aaa'),('abcd'),('abc123'),('ab56def'),('xyz'),('9999'),('3946'),('9236'),('854')

SELECT STUFF(NAME, 1, PATINDEX('%[a-zA-Z]%', NAME) - 1, '')

FROM #Names

If digits aren't always at the beginning, you could use the pattern splitter to remove them and then join the values together.

SELECT CAST( (

SELECT Item + ''

FROM #Names x

CROSS APPLY MEXDWREP..PatternSplitCM(Name, '%[^0-9 ]%')

WHERE Matched = 1

AND x.Name = n.Name

ORDER BY ItemNumber

FOR XML PATH('')) AS varchar(max))

FROM #Names n

You can read about the PatternSplitCM in here: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

And about the method to join the values in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

There might be faster ways to do this, but would require more code and testing for something specific. If you need it, I could try to get something done.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rodjkidd SSCoach Points: 15883 More actions · Answer 5

Luis,

Thanks for that, I had a complete brain freeze on how to check for A-Z and a-z. I tried A-z - which was wrong. 🙂

I did also think there was another way without the double REVERSE - an old VB trick I've used in the past.

I like the use of STUFF - nice, simple and elegant. Must remember to try that as alternative in the future.

And good point if the actual data is more complex than the given examples, Dwain's article is the way to go.

Rodders...

mickyT SSChampion Points: 10360 More actions · Answer 6

Hi

If the OP requirements are as simple as remove all digits from the string, I would tend to just use a nested replace within a LTRIM to tidy up the leading space.

Ugly looking, but quite efficient.

SELECT LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

Name,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0',''))

FROM #Names

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

I always forget to use the nested replace solutions. It should outperform the splitter.

I wish that SQL Server could include a "pattern replace" to get something like

SELECT LTRIM( PATREPLACE( Name,'%[0-9]%',''))

FROM #Names

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 8

Luis Cazares (4/3/2014)
I always forget to use the nested replace solutions. It should outperform the splitter.
I wish that SQL Server could include a "pattern replace" to get something like
SELECT LTRIM( PATREPLACE( Name,'%[0-9]%',''))
FROM #Names

Right with on that. Builtin functions for regular expressions would be nice too:-D