SQL column data convert to standard format 2008r2

Question

I am in the process of trying to migrate a manual MS Access process over to a automated SQL process

I have every working as intended except for the last column.

Here is sample table data and then below is what I am trying to get the data to change into.

10903  
1091    
319     
2968A   
782R 

Needs to look like this

10903
01091
00391
02968
00782

Any string in first column needs to be stripped down to numbers and if the string is then less then five to add zeros to the front.

I understand how the conversation takes place in Access and if had access to SQL Server 2012 or greater it would be ok.

Thoughts?

Am I looking at having to use SET and a long line of nested IF's?

Answer 1

Borrowing the answer from here, and assuming that you always want a five-number output, you'd be looking at something like this:

DECLARE @textval NVARCHAR(30)
SET @textval = '2968A'

SELECT RIGHT('00000' + CAST(LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1) AS VARCHAR(5)), 5)

If your number could be more than five digits, though, this will need to be modified to take that into account. Replace the two "8000" values with the length of your varchar column, regardless.

To run this against your table, replace all occurrences of @textval with your column name (and add the FROM/WHERE/any other clauses).

Answer 2

Took the answer from here and padded the result. There's no need to cast to varchar because you already have characters.

create table YourTable
(
  Data varchar(100)
);

insert into YourTable values
('10903'),
('1091'    ),
('319'  ),
('2968A'),
('782R')

Select Right('00000'+ isnull(Left(
             SubString(Data, PatIndex('%[0-9.-]%', Data), 8000), 
             PatIndex('%[^0-9.-]%', SubString(Data, PatIndex('%[0-9.-]%', Data), 100) + 'X')-1),
        ''),5)

from YourTable

http://sqlfiddle.com/#!3/bd3c3/5