Solved

how do i remove numbers from data value?

Posted on 2009-07-15
12
207 Views
Last Modified: 2012-08-13
't1e2s3t' --> 'test'

0
Comment
Question by:Amien90
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 300 total points
Comment Utility
SELECT dbo.RemoveDigits('a2s3dff4fdf556gjgh098')  

returns

asdfffdfgjgh


CREATE FUNCTION dbo.RemoveDigits(@MyText varchar(4000))

RETURNS varchar(4000)

AS

BEGIN
 

RETURN replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@MyText,'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'0','')
 

END

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
this will do.
declare @ActualVariable nvarchar(1000) = 't1e2s3t'

DECLARE @ModifiedVariable nvarchar(1000) = ''

 SELECT @ModifiedVariable = @ModifiedVariable + CASE WHEN number LIKE '[^0-9]' THEN number ELSE '' END 

   FROM (SELECT SUBSTRING(@ActualVariable,number,1) AS number 

           FROM (SELECT number FROM master..spt_values 

                  WHERE type = 'p' AND number BETWEEN 1 AND LEN(@ActualVariable)) as t) as t 

 select replace(replace(@ModifiedVariable,'(',''),')','')

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 100 total points
Comment Utility
you can create a function with code provided and use that function.

select dbo.fnOnlyString('t1e2s3t')

create function dbo.fnOnlyString(@accountname nvarchar(1000)) returns nvarchar(1000) as

begin

DECLARE @account_name nvarchar(1000)

    SET @account_name=''

 SELECT @account_name = @account_name + CASE WHEN number LIKE '[^0-9]' THEN number ELSE '' END 

   FROM (SELECT SUBSTRING(@accountname,number,1) AS number 

           FROM (SELECT number FROM master..spt_values 

                  WHERE type = 'p' AND number BETWEEN 1 AND LEN(@accountname)) as t) as t 

 return replace(replace(@account_name,'(',''),')','')

end

Open in new window

0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
Comment Utility
Oh well I'll pitch in with 2 cents too. Only difference is I am using PATINDEX:
-- FUNCTION CALL

Select dbo.fn_GetString('t45es954t')
 

-- CREATE FUNCTION

CREATE FUNCTION fn_GetString (@Numstr nvarchar(max))

   RETURNS nvarchar(max)

AS

BEGIN

WHILE PATINDEX('%[^A-Z]%',@NumStr)> 0

SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^A-Z]%',@NumStr),1),'')

RETURN @NumStr 

END

Open in new window

0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
I have a few notes to other solutions:
Sharath: Your approach is very interesting but very slow and it works for strings up to 2047 characters only (your function removes parenthesis which is not requested)
pssandhu: Removing digits does not mean to leave just letters.

The "nested REPLACEs" approach looks stupid but it is easy to understand and very fast.

BTW, to implement this in Visual FoxPro is a peanut because the function is built-in already:

   CHRTRAN('s23d3fc456sX','1234567890','')  returns  'sdfcsX'

Now when the Visual FoxPro development team is working on SQL Server we may expect some useful function implemented soon...
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 17

Expert Comment

by:pssandhu
Comment Utility
pcbella: Good point! There might be apostrophies or quotes that might be part of the string that needs to be retained as well. In that case I would do it this way; it's similar to your approach but instead of using nested Replace, I use a loop :-)
Cheers!

CREATE FUNCTION fn_GetString (@Numstr nvarchar(max))

   RETURNS nvarchar(max)

AS
 

BEGIN
 

DECLARE @Counter INT

SET @Counter = 0
 

WHILE @Counter <= 10

 BEGIN

	SET @NumStr = REPLACE(@NumStr,@Counter,'')

	SET @Counter = @Counter+1

 END
 

RETURN @NumStr 
 

END

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
tested my solution and its working for upto 2047 characters. And its not even taking 1 second for execution. I dont think its extremely slow. but agree with you on the length of the string.
Amien90, - do you have strings with 2000+ characters?  
0
 

Author Comment

by:Amien90
Comment Utility
nope. . never 2000+ chars ... i took a function for this problem .. thanks all..

(strange that SQL doesnt have a standard option for this!)
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
may i know the reason for 'B'?
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
I agree, it should be "C"  :-)))

May I know the reason of accepting late, not fully functional, and slow solution ??  

What's wrong with dbo.RemoveDigits finction from answer ID:24864916 ?

:-)))
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now