Getting a certain part of the string in T-SQL

Question

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?

t-clausen.dk · Accepted Answer · 2012-06-21 14:10:35Z

3

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

edited Jun 21, 2012 at 14:10

answered Jun 21, 2012 at 8:26

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andriy M Over a year ago

Nice, although it won't work correctly if there's no non-numeric prefix.

t-clausen.dk Over a year ago

@AndriyM thx for your comment, I agree that there were some issues reguarding corrupt data, I have tried to compensate to avoid problems like that.

Andriy M Over a year ago

Yup, seems to work. Alternatively: select stuff(expression, 1, patindex('%[0-9]%', expression + '0') - 1, '') from @t.

Jcis · Accepted Answer · 2012-06-21 07:49:17Z

1

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)

answered Jun 21, 2012 at 7:49

Jcis

1732 silver badges16 bronze badges

Comments

Jeff Moden · Accepted Answer · 2012-06-25 05:18:37Z

1

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)

answered Jun 25, 2012 at 5:18

Jeff Moden

3,5392 gold badges29 silver badges27 bronze badges

Comments

Vishwanath Dalvi · Accepted Answer · 2012-06-21 06:38:21Z

0

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

answered Jun 21, 2012 at 6:38

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

2 Comments

Jeff Moden Over a year ago

Really really really try to get out of the habit of using While loops. Considering that you know about PATINDEX, there's just no need for them here.

R. Schreurs Over a year ago

The question was about getting the numeric part, not the textual part.

David Brabant · Accepted Answer · 2012-06-21 06:42:09Z

0

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))

answered Jun 21, 2012 at 6:42

David Brabant

43.9k16 gold badges93 silver badges119 bronze badges

1 Comment

Seph Over a year ago

shouldn't you be using LEN instead of DATALENGTH in case the string is actually nvarchar?

Collectives™ on Stack Overflow

Getting a certain part of the string in T-SQL

5 Answers 5

3 Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related