MID in SQL

Team - I have a field in a table where the data is in below format.

MATA YES [2000534870]
MATA YES [2000534870] NO
AAIG NOW [2000555565] ALTREK

I need to extract only the number in between the brackets. What query would get me this result? pls help
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
 
Leo TorresSQL DeveloperCommented:
if you dont want a replace you can do this one

SELECT SUBSTRING(@str,charindex('[',@str,0)+1, (charindex(']',@str,0)-charindex('[',@str,0)-1 ))

Open in new window

This one is less efficient since it has to parse twice.
0
 
Leo TorresSQL DeveloperCommented:
try
DECLARE @str varchar(100)

SET @str = 'MATA YES [2000534870]'


SELECT Replace(SUBSTRING(@str,charindex('[',@str,0)+1,Len(@str)),']','')

Open in new window



You must come from a Excel background sql does not have a MID function for strings.
0
 
Saurabh Singh TeotiaCommented:
Try this...

Replace @field with your actual field name...

select SUBSTRING(@field,charindex("[",@field)+1,CHARINDEX("]",@field)-CHARINDEX("[",@field)-1)
from your table

Open in new window


Saurabh...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.