Search code examples
stringsql-server-2008charindex

Getting a string value that is between two characters

I need to get the value that is between !03 and !03.

Example:

JDC!0320151104!03OUT

I should get following string in return: 20151104

NOTE: The string isn't always 22 characters long, but I am only concerned with the value that is between !03 and !03.

This is what I have so far. I couldn't make any progress further than this:

SELECT 
SUBSTRING(
RegStatsID, 
CHARINDEX('!', RegStatsID) + 3, 
CHARINDEX('!', REVERSE(RegStatsID))
)
 From TableX
Solution

  • Great that you found a solution!

    This might be better:

    By replacing the "!03" with XML-tags you can easily pick the second "node". Your string will be transformed into <x>JDC</x><x>20151104</x><x>OUT</x>:

    DECLARE @test VARCHAR(100)='JDC!0320151104!03OUT';

SELECT CAST('<x>' + REPLACE(@test,'!03','</x><x>') + '</x>' AS XML).value('/x[2]','datetime')

    One advantage was to get the value between the two "!03" typed. In this case you get a "real" datetime back without any further casts. If the value there is not a datetime (or date) in all cases, you just use nvarchar(max) as type.

    Another advantage was: If you - why ever - need the other values later, you just have them with .value('/x[1 or 3]'...)