Getting a certain part of the string in T-SQL

问题:

I have a string like prop234 the alphabet part has fixed length but the numeric part may be of any length

I have to get the numeric part from the string in T-SQL (on SQL Server 2008)

I tried with SUBSTRING function but I don't know the length of the numeric part so can't provide third parameter i.e. length

SUBSTRING ( expression ,start , length )

I know the start index but length can be anything

One solution is

SELECT substring([ColumnName], 5, LEN(ColumnName) - 4)

as start index is fixed i.e. length of alphabet part is 4 (fixed)

Is there any better solution for this and what if the length of alphabetic part is not constant?

回答1:

select stuff('prop234', 1,4,'')

and if the length is not a constant:

declare @t table(expression varchar(100))
insert @t values('propprop234')

select stuff(expression, 1, patindex('%_[0-9]%', expression), '') from @t

EDIT: To make sure bad data is handled, such as no text first or no number last, here is a slightly different approach:

select stuff(expression, 1,patindex('%[^0-9][0-9]%', expression + '0'), '') 
from @t 

回答2:

This should get the number no matter how long it is or how long the alphabetic part is:

declare @str as varchar(50)
declare @pos as integer

set @str = 'abdfgdfghhcd123456784564690'
set @pos =  PATINDEX('%[0-9]%',@str)

select substring(@str, @pos,len(@str)-@pos+1)

回答3:

You simply don't need to calculate the length of the remainder of the string. The following will work just fine.

SELECT substring([ColumnName], PATINDEX('%0-9%',[ColumnName]),8000) 

回答4:

You can use a User defined function (UDF) to get only alphabets from a string which contains numeric, special chars etc.

IF object_id('dbo.GetAlpha') is not NULL
   DROP FUNCTION GetAlpha
GO

CREATE FUNCTION GetAlpha (@strAlpha VARCHAR(256)) 
RETURNS VARCHAR(256)
AS

BEGIN
DECLARE  @intAlpha INT
SET      @intAlpha = PATINDEX('%[^a-z]%', @strAlpha)

BEGIN
WHILE    @intAlpha > 0
BEGIN
SET @strAlpha = STUFF(@strAlpha, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^a-z]%', @strAlpha)
END
END
RETURN ISNULL(@strAlpha,0)
END
GO

Example how to use function

select dbo.getalpha('stackoverflow102020');

-------------
stackoverflow

回答5:

If your "prefix" is always 4 chars, you can writesomething like:

declare @v varchar(max) = 'prop234'

select substring(@v, 5, DATALENGTH(@v))

select @v = 'prop567890'

select substring(@v, 5, DATALENGTH(@v))