如何在SQL Server中使用substring和PatIndex

How to substring data. For example, I have data like this:

如何对数据进行子串。例如，我有这样的数据：

+----------------------+
|John123123412412wqeqw |
|May1231243334234wawdd |
|Jo02930124010284jahdj |
|dy827837127912938hygb |
+--------------------- +

I want the output to be like this:

我希望输出如下：

+----------------------+
|John                  |
|May                   |
|Jo                    |
|dy                    |
+--------------------- +

As of now I don't understand how to Apply Substring and charindex in my script.

截至目前，我不明白如何在我的脚本中应用Substring和charindex。

Thanks in advance!

提前致谢！

2 个解决方案

#1

You can try LEFT and PATINDEX with a pattern to match the numbers like PATINDEX('%[0-9]%', 'John123123412412wqeqw').

您可以尝试使用模式匹配LEFT和PATINDEX以匹配PATINDEX（'％[0-9]％'，'John123123412412wqeqw'）等数字。

Sample code

示例代码

DECLARE @Text VARCHAR(500);
SET @Text = 'John123123412412wqeqw';

SELECT LTRIM(RTRIM(LEFT(@Text, PATINDEX('%[0-9]%', @Text) - 1)))

You can do this from the table as below

您可以从下表中执行此操作

SELECT 
   LTRIM(RTRIM(LEFT(ColumnName, PATINDEX('%[0-9]%', ColumnName) - 1))) 
FROM 
   [Table1]

#2

Best way to handle the strings and there manipulation is in application side and not in database side. But if you required this then you can use PATINDEX along with SUBSTRING to get what you want,

处理字符串的最佳方法是操作在应用程序端而不是数据库端。但是如果你需要这个，那么你可以使用PATINDEX和SUBSTRING来获得你想要的东西，

SELECT PATINDEX('%[^0-9]%',stringValueCol) 'Position of NonNumeric String Position',
SUBSTRING(stringValueCol,PATINDEX('%[^0-9]%',stringVlaueCol),PATINDEX('%[^A-Z]%',stringValueCol)-1) 'NonNumeric String'
FROM
  myTable

But I would still suggest do this manipulations in code and not on database side.

但我仍然建议在代码中而不是在数据库端进行这种操作。

#1