• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

remove numbers in a sql string

I have a field from where I want to remove the numbers.
Examples
12 Me me
125 just think
56 again
I doesn't have a pattern. Should I use PATINDEX or CHARINDEX?
0
Ilianam
Asked:
Ilianam
  • 2
1 Solution
 
mcmonapCommented:
Hi Ilianam,

A simple loop might do what you want, this will remove numbers 0 to 9:

--start code
DECLARE @i INT
SET @i = 0
WHILE @i < 10
BEGIN
      UPDATE <your table name> SET <you column name> = REPLACE(col1,@i,'')
      SET @i = @i+1
END
--end code
0
 
IlianamAuthor Commented:
I think in that case I will need a cursor in order to navigate through the whole table. Is that true?
0
 
RaisinJCommented:
No, your will not need a cursor, mcmonap's code above will update the netire column.  It'll just simply replace any intergers, 0-9 with ''.  If they do not exist in a certain record, nothing will take place.
0
 
RaisinJCommented:
If you need to update mutiple columns, you can include that in the one update statement.  e.i.:

UPDATE <your table name> SET <col1> = REPLACE(col1,@i,''), <col2> = REPLACE(col2,@i,''), <col3> = REPLACE(col3,@i,'')
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now