修剪任何前导或尾随字符 [英] Trimming any Leading or trailing characters

问题描述

我有一张表格，里面有一些原始数据.我的要求是修剪给定字符串列中的任何前导或尾随空格和运算符字符.运算符字符的示例包括 + - .><= :;"

I have a table where I have some raw data. My requirement is to trim any leading or trailing spaces and operator characters in the given string column. Examples of operator characters include + - . > < = : ;"

示例:

值 +Payment 应修剪为 Payment
值 ->300 Write 应修剪为 300 Write

Value +Payment should be trimmed to Payment
Value ->300 Write should be trimmed to 300 Write

推荐答案

你可以试试这个:

DECLARE @tbl TABLE(YourString VARCHAR(100));
INSERT INTO @tbl VALUES('+Payment'),('->300 Write'),('-:<Test,:%');

SELECT SUBSTRING(YourString,A.posFirst,A.posLast-A.posFirst+2)
FROM @tbl 
OUTER APPLY(SELECT PATINDEX('%[a-zA-Z0-9]%',YourString) AS posFirst
                  ,LEN(YourString)-PATINDEX('%[a-zA-Z0-9]%',REVERSE(YourString)) AS posLast) AS A

结果

Payment
300 Write
Test

您可以在模式中添加任何允许的字符...

You can add any allowed character to the pattern...

这篇关于修剪任何前导或尾随字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！