CODEWITHSUNDEEP
sql-servert-sqlsubstring
user2216540
user2216540

Reputation: 83

Substring not working in all my cases, how could I fix it?

I have a substring query which works but it breaks if there are no wordings after my searched query. I don't know how to fix it.

I tried removing and changing the numbers +1 +2 -1 -2 after the len but no chance to get it work. 

declare 
@FullText nvarchar(400) = 'My Code in ''ABC123, CDE456'' and my digit 1',
@KnownBeginning nvarchar(400) = 'Code in ''',
@KnownEnding nvarchar(400) = ''' '

select SUBSTRING(@FullText, CHARINDEX(@KnownBeginning, @FullText) + LEN(@KnownBeginning) , 
            CHARINDEX(@KnownEnding,@FullText) - (CHARINDEX(@KnownBeginning, @FullText)
            + LEN(@KnownBeginning)))


--@FullText2 nvarchar(400) = 'My Code in ''ABC123, CDE456'''

The code works above, but if we replace the @FullText to the @FullText2 which there is no text after '' - "and my digit 1" the select breaks. It seems that the substring works if there are any text after the searched string is found. But if there are nothing else after ''ABC123, CDE456'' it says:

Msg 537, Level 16, State 3, Line 7 Invalid length parameter passed to the LEFT or SUBSTRING function.

The select should return always, if there is or there isn't text after quotes.

Upvotes: 1

Views: 565

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

You might try something along this:

DECLARE @tbl TABLE(YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('My Code in ''ABC123, CDE456'' and my digit 1')
,('My Code in ''ABC123, CDE456''');

SELECT t.YourString
      ,A.CastedToXml.query('.') AS ThisIsHowItLooksLike
      ,A.CastedToXml.value('/x[2]','varchar(100)') AS TheSecondElement
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(t.YourString,'''','</x><x>') + '</x>' AS XML)) A(CastedToXml);

Click the XML node to see the intermediate, casted value. We can use .value() to pick the second <x>, which is your data.

Alternatively you can follow your own approach along this:

DECLARE @KnownBeginning nvarchar(400) = 'Code in ''', 
        @KnownEnding nvarchar(400) = ''''; --<-- Without the blank!

SELECT t.YourString
      ,SUBSTRING(t.YourString,A.StartPosition,B.EndPosition-A.StartPosition) AS TheCodes
FROM @tbl t
CROSS APPLY(SELECT CHARINDEX(@KnownBeginning,t.YourString) + LEN(@KnownBeginning)) A(StartPosition)
OUTER APPLY(SELECT CHARINDEX(@KnownEnding,t.YourString,A.StartPosition+1)) B(EndPosition);

I use APPLY to calculate values row-wise. This allows for using computed values similar to variables in procedural approaches. The first APPLY computes the starting point, while the second APPLY uses the StartPosition as offset. Therefore we do not need the blank after the quote.

Upvotes: 1

Related Questions