Substring from position to position in SQL server
Substring from position to position in SQL server
Is there an easy way to extract a string from a defined position to another defined position? (unlike the Substring Function where the last parameter is the length of the string being extracted).
There is no "
EXTRACT" function with the parameters Start Position and End Position. SUBSTRING, operates with Start Position and Number of Characters, just like most programming languages (I say most, as I'm sure there's an exception, I just don't know one). If you want a function like that, you'd have to write one (but why, I don't know, when SUBSTRING already serves the exact same purpose and the second parameter can easily be derived).– Larnu
Aug 30 at 9:48
EXTRACT
Start Position
End Position
SUBSTRING
Start Position
Number of Characters
SUBSTRING
2 Answers
2
Yep, basic maths:
declare @v varchar(200),
@p1 int,
@p2 int
select @v = 'One Two Three',
@p1 = 5,
@p2 = 7
select substring(@v, @p1, (@p2-@p1)+1)
Thanks for that. I was actually looking for another function that I might not be familiar with ... But that should work for sure.
– IZ4
Aug 30 at 9:29
If you have @pos and @endpos, you might find this useful:
@pos
@endpos
select stuff(stuff(@str, @endpos + 1, len(@str), ''), 1, @pos - 1, '')
This eliminates the calculation of the length.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
length = pos2-pos1, doesn't it?
– Ivan Starostin
Aug 30 at 9:20