How to remove letters from beginning and end of varchar column in SQL when not authorized to use UPDATE function

Question

I have a varchar(20) column in SQL that contains some values with letters in the beginning and/or end of the value (i.e. AD5376276 or AD574638BD or 324523BD).

I want to eliminate any letters from these values in this column so the result will be only numeric character values. I am doing this in SQL.

I have being trying something along the lines of this in SQL

SELECT 
    LTRIM('%[a-z]%', txn.transaction_number) 
    RTRIM(txn.transaction_number, '%[a-z]%')
FROM 
    arrt.tblAgingTransactionDetail_DailyR txn;

Thanks!

Please edit & add tags for the specific database platform are you using. Are there always only 2 letters at the start|end ? — Alex K., Feb 19 '18 at 17:39
Which dbms?????? The syntax are different for different dbms. — Eric, Feb 19 '18 at 18:36
This is for SSMS. In some cases the value is has anywhere from 1-3 characters at the beginning and/or end. — Trent, Feb 20 '18 at 13:40

score 1 · Answer 1 · answered Feb 19 '18 at 17:40

1

Assuming you are using Sql Server 2008, please check the example below, you are looking for proper usage of LEFT, PATINDEX and SUBSTRING functions:

DECLARE @value NVARCHAR(30)
SET @value = 'QWE SAGQER #%&*| 123'

SELECT LEFT(SUBSTRING(@value, PATINDEX('%[0-9.-]%', @value), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(@value, PATINDEX('%[0-9.-]%', @value), 8000) + 'X') -1)

For more examples: T-SQL select query to remove non-numeric characters

answered Feb 19 '18 at 17:40

Eray Balkanli

7,752
11
48
82

Thanks! this worked! My final code looked like this: – Trent Feb 20 '18 at 15:52
@Trent you are very welcome! I appreciate if you may select the answer as the "correct answer" then by clicking the "tick" on the left. Thanks. – Eray Balkanli Feb 20 '18 at 15:56
I have voted for your answer, but until my reputation is above 15 points it will not register. Thanks though! – Trent Feb 20 '18 at 20:52

score 1 · Answer 2 · answered Feb 20 '18 at 15:53

Thanks for being patient with me as this was my first time posting.

The final code I used that worked was as follows:

SELECT LEFT(SUBSTRING(transaction_number, PATINDEX('%[0-9.-]%', transaction_number), 8000),
       PATINDEX('%[^0-9.-]%', SUBSTRING(transaction_number, 
       PATINDEX('%[0-9.-]%', Transaction_Number), 8000) + 'X') -1) AS transaction_number

score 0 · Answer 3 · answered Feb 19 '18 at 17:40

Regular expressions might help. This is an Oracle example (as you didn't specify your database) so you might need to adjust it.

SQL> with test (col) as
  2    (select 'AD5376276'  from dual union
  3     select 'AD574638BD' from dual union
  4     select '324523BD'   from dual
  5    )
  6  select col, regexp_substr(col, '\d+') result
  7  from test;

COL        RESULT
---------- ----------
324523BD   324523
AD5376276  5376276
AD574638BD 574638

SQL>

How to remove letters from beginning and end of varchar column in SQL when not authorized to use UPDATE function

3 Answers3