3

I'm struggling to combine two expression into one so that I can remove trailing 'mm' chars of a varchar column which holds values like 3.214mm and use those chars as numeric values.

The problem I have is that there can be also null or empty string values and I can't find a way to combine both expressions below.

Example: SQLfiddle

DECLARE @string varchar(128)
SET @string = '4.123mm'
SELECT ISNULL(NULLIF(@string,''),NULL) As MyString ;

DECLARE @createNumber varchar(128)
SET @createNumber = '4.123mm'
select LEFT(@createNumber, NULLIF (LEN(@createNumber) - 2, - 1))As MyNumber
nathanchere
  • 8,008
  • 15
  • 65
  • 86
Tony Clifton
  • 703
  • 3
  • 14
  • 27
  • A bit tacky, but do you know the measurements are "mm"? Might consider Replace of "mm" with "". – asantaballa Sep 27 '13 at 11:25
  • Also some interesting ideas if a bit procedural here: http://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server – asantaballa Sep 27 '13 at 11:29

2 Answers2

2
DECLARE @createNumber varchar(128)
SET @createNumber = ''
select reverse(stuff(reverse(@createNumber), 1,2, ''))

This will return null if createnumber is shorter than 2 characters.

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
0

One way to handle mm/''/null;

select cast(isnull(replace('0' + @createNumber, 'mm', ''), 0) as decimal(6,3))
Alex K.
  • 171,639
  • 30
  • 264
  • 288