Remove leading and trailing characters except between the digits

user_5695953 0
Viewed 118

Here is an example of input value:

00080a7a00

and the expected output of this input:

80a7

To get the expected output i tried the following query:

set @ttt='00080a7a00'
select SUBSTRING(@ttt,PATINDEX('%[1-9]%',@ttt),LEN(@ttt))

And i am getting this output:

80a7a00

So i am looking for the correct query to have the expected output.

tsql sql-server sql
2 Answers

If you want to ... remove leading and trailing characters except between the digits ..., starting from SQL Server 2017, you may use TRIM() to remove the specified characters from the start and the end of a string (TRIM() function accepts literal or variable containing characters that should be removed as first parameter):

DECLARE @text varchar(100)
DECLARE @chars varchar(100)

SELECT @text = '00080a7a00'
SELECT @chars = CONCAT(
   LEFT(@text, PATINDEX('%[1-9]%', @text) - 1),
   LEFT(REVERSE(@text), PATINDEX('%[1-9]%', REVERSE(@text)) - 1)
)   
SELECT TRIM(@chars FROM @text)

Result:

80a7
user_65780800

You can use REVERSE to find the trailing characters:

SELECT SUBSTRING(@ttt,PATINDEX('%[1-9]%',@ttt),2+LEN(@ttt) -PATINDEX('%[1-9]%',@ttt) - PATINDEX('%[1-9]%',REVERSE(@ttt)))
user_136363760
Related