如何分开字母和数字？ [英] How to separate letters and numbers?

问题描述

DECLARE @A VARCHAR(90) SET @A='AAAABBB123MNV59KFN'

如何分隔字母和数字？



如何获得输出？

How to separate letters and numbers?

How to get the output?

推荐答案

基本上，不要。

SQL在字符串处理方面非常差 - 除非数字总是处于固定位置，否则在表示语言中这样做要好得多。你真正想要使用的是一个正则表达式 - 对他们来说这是一项微不足道的任务。



如果它是固定位置而你必须在SQL中执行它，那么看看使用 SUBSTRING [ ^ ]， LEFT [ ^ ]和权利 [ ^ ]功能。

但即便如此，我可能仍然会这样做用表示语言而不是SQL。

Basically, don't.
SQL is very poor at string handling - unless the numbers are always in fixed positions, you are much better off doing this in your presentation language. What you really want to use is a Regex - it's a trivial task for them.

If it is fixed positions and you must do it in SQL, then look at using the SUBSTRING[^], LEFT[^], and RIGHT[^] functions.
But even then, I'd probably still do it in a presentation language rather than SQL.

在这里你去：



（保留所有权利？http：// blog。 sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha -numeric-string-udf-for-get-numeric-numbers-only /）



编辑，上面的文章展示了如何创建函数：

Here you go:

(all rights reserved? http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/)

Editorially, the above article shows how to create the function:

-- Get numeric --

CREATE FUNCTION [dbo].[udf_OO_inBuilt]
(@strAlphaNumeric VARCHAR(256))
   RETURNS VARCHAR(256)
    AS
     BEGIN
       DECLARE @intAlpha INT
       SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
     BEGIN
        WHILE @intAlpha > 0
     BEGIN
       SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
       SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
     END
     END
     RETURN ISNULL(@strAlphaNumeric,0)
    END
GO

运行一个查询使用那个不错的函数：

Run a query using that nice function:

DECLARE @A VARCHAR(90) 
SET @A='AAAABBB123MNV59KFN'

SELECT [dbo].[UDF_OO_inBuilt](@A) -- 12359

基于TSQL通配符促进的附加值：

Added value, based upon TSQL wildcard facilitation:

-- Get alphabetic --

CREATE FUNCTION [dbo].[udf_OO_outBuilt]
(@strAlphaNumeric VARCHAR(256))
   RETURNS VARCHAR(256)
    AS
     BEGIN
       DECLARE @intAlpha INT
       SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric)
     BEGIN
        WHILE @intAlpha > 0
     BEGIN
        SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric )
     END
     END
     RETURN ISNULL(@strAlphaNumeric,0)
  END
GO

再次运行查询sqprouthority思想的代码项目剥离：

Run query again using codeproject spin-off of sqlauthority thoughts:

DECLARE @A VARCHAR(90) 
SET @A='AAAABBB123MNV59KFN'

SELECT [dbo].[UDF_OO_outBuilt](@A) -- AAAABBBMNVKFN

我将两者结合起来并用可选变量灌注以使其更加健壮。

I'd combine the two and perfuse with optional variables to make more robust.

这篇关于如何分开字母和数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！