Extract part of a string in SQL

Question

In SQL Server 2008 R2 I need to extract from a string everything between the character 50 and 60. I've searched for a similar function on the internet but didn't really find something which can address the problem.

I can do it on Excel with a formula like: MID(A2, 50, 10)

SubString() - technet.microsoft.com/en-us/library/ms187748.aspx — gvee
– gvee, Commented Jan 31, 2014 at 16:01

Hogan · Accepted Answer · 2014-01-31 16:01:40Z

5

The sql function is called substring

  substring(fieldname,50,10)

answered Jan 31, 2014 at 16:01

Hogan

70.7k10 gold badges82 silver badges119 bronze badges

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

Comments

MHeath · Accepted Answer · 2017-11-22 17:40:28Z

0

If you need more flexibility, for instance if the separation does not occur in the same place each time you can use CHARINDEX. Here are a couple examples.

REVERSE(LEFT(REVERSE(fieldname),CHARINDEX(' ',REVERSE(fieldname))))

If your string is "AA 1235 A9" the output will be A9.

REPLACE(STUFF(fieldname,1,CHARINDEX('- ',fieldname),''),REVERSE(LEFT(REVERSE(fieldname),CHARINDEX(' ',REVERSE(fieldname)))),'')

Adding on to the first bit of code you can also pull the middle portion which will result in 1235. The ' ' is flexible and if there is a hyphen, asterisk or some other symbol/identifier you need to use then just use that instead of the space.

answered Nov 22, 2017 at 17:40

MHeath

818 bronze badges

Collectives™ on Stack Overflow

Extract part of a string in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related