Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
InNomina
Starting Member
40 Posts |
Posted - 2013-01-24 : 13:24:54
|
I have a column that has mixed alpha numeric<letters and numbers> in no particular format. I need to extract the numbers to another column.The numbers are in different places< although always togather>example: Cell 1: 3 Audit Year Cycles or Current Year + 10 (which ever is longer)Cell 2: Active + 5Cell 3: Termination of Plan + 10So I need only the numbers that are together i.e. in cell 1 the 10, cell 2 the 5 and cell 3 the 10Is this possible?-------------------------"If you never fail, you're not trying hard enough" |
|
InNomina
Starting Member
40 Posts |
Posted - 2013-01-24 : 16:25:23
|
Ok, so the solution was to create a function.-------CREATE FUNCTION [dbo].[fnRemoveNonNumeric]( @BadString nvarchar(20))RETURNS nvarchar(20)ASBEGIN DECLARE @nPos INTEGER SELECT @nPos = PATINDEX('%[^0-9_]%', @BadString) WHILE @nPos > 0 BEGIN SELECT @BadString = STUFF(@BadString, @nPos, 1, '') SELECT @nPos = PATINDEX('%[^0-9_]%', @BadString) END RETURN @BadStringENDTo test my function I used...update test set import = [dbo].[fnRemoveNonNumeric](IMPORT)Worked like a charm!Hope this saves a user out there hours of frustration!-------------------------"If you never fail, you're not trying hard enough" |
|
|
|
|
|