SQL Command To Trim Output

Question

I need to rip out the digits of a number i'm returning. I have them in a listing like X0006, X0130, and X1030. I need to pull out X's and 0's and return 6,130,1030 . I've tried RTRIM like this. Anyone have any ideas on what I need to do? I'm sure i'm missing something easy.

Thanks

Answer 1

I used replace to get rid of the X and just copied/pasted into excel to get the preceding zeros off

Answer 2

Here is a function you can create that will do exactly what you're asking for and any future get digits needs.

CREATE FUNCTION [dbo].[fn__getDigits] 
( 
@string VARCHAR(50) 
) 
RETURNS int 
AS 
BEGIN 
-- declare variables 
DECLARE 
@digits VARCHAR(50), 
@length INT, 
@index INT, 
@char CHAR(1) 

-- initialize variables 
SET @digits = '' 
SET @length = LEN(@string) 
SET @index = 0 

-- examine each character 
WHILE (@length >= @index) 
BEGIN 
SET @char = SUBSTRING(@string, @index, 1) 
SET @index = @index + 1 

-- add to the result if the character is a digit 
IF (48 <= ASCII(@char) AND ASCII(@char) <= 57) 
BEGIN 
SET @digits = @digits + @char 
END 
END 

-- return the result 
RETURN cast(@digits as int)
END
GO


select [dbo].[fn__getDigits]('X0006')
select [dbo].[fn__getDigits]('X0130')
select [dbo].[fn__getDigits]('X1030')

Answer 3

Use replace to convert X and O to empty strings, cast to a integer to remove leading zeros.

Sample code below.

-- Simple number table
create table #nums
( my_id int identity(1,1),
  my_text varchar(32)
);

-- Simplte test data
insert into #nums (my_text)
values
('X10X30X'),
('O00O30O');


-- Remove characters & convert to int
SELECT CAST (REPLACE(REPLACE(my_text, 'X', ''), 'O', '') AS INT) as my_number 
FROM #nums

Sample output below.