Return Word in String - SQL

Question

I am using SQL Server, how can I return the account numbers from the below string in SQL? Below is all in 1 column and 1 string of NVarchar()

Bank Name: eewweew Chemnitz Bank Account Address: weweweewew Zwickau 12345 dfdfdfdfd fdfdfdfdf. 1-3 Beneficiary Name: Roswitha Haupt-Elster Account Number: TheValueToReturn SWIFT/BIC Code: VVHHH SortCode: sfsffsfsa IBAN: wdffwfafsafsafs

Thus, only return: TheValueToReturn from the string above

Something that will return the value between Number: and SWIFT

Answer 1

SQL Server isn't the best thing to do this in, personally i would do it in the presentation layer.

This, however, works for the example we have, however, you might want to test some more. It also assume that every row has an Account number (prefixed with 'Account Number:') and that it has more data after that value:

CREATE TABLE #Sample (JunkText nvarchar(4000));

INSERT INTO #Sample
VALUES('Name: eewweew Chemnitz Bank Account Address: weweweewew Zwickau 12345 dfdfdfdfd fdfdfdfdf. 1-3 Beneficiary Name: Roswitha Haupt-Elster Account Number: TheValueToReturn SWIFT/BIC Code: VVHHH SortCode: sfsffsfsa IBAN: wdffwfafsafsafs')
GO
SELECT *, SUBSTRING(JunkText, PI1.AN + LEN('Account Number: '), CI1.AN - (PI1.AN -2)) As AccountNumber
FROM #Sample
     CROSS APPLY (VALUES(PATINDEX('%Account Number:%',JunkText))) PI1(AN)
     CROSS APPLY (VALUES(CHARINDEX(' ',JunkText, PI1.AN + LEN('Account Number: ')))) CI1(AN)

GO

DROP TABLE #Sample;

Answer 2

This might help you:

DECLARE @String VARCHAR(MAX)

SET @String = 'Bank Name: eewweew Chemnitz Bank Account Address: weweweewew Zwickau 12345 dfdfdfdfd fdfdfdfdf. 1-3 Beneficiary Name: Roswitha Haupt-Elster Account Number: TheValueToReturn SWIFT/BIC Code: VVHHH SortCode: sfsffsfsa IBAN: wdffwfafsafsafs'

SELECT REPLACE(SUBSTRING(LEFT(@String, CHARINDEX('SWIFT', @String) - 1), CHARINDEX('Number:', LEFT(@String, CHARINDEX('SWIFT', @String) - 1)), LEN(LEFT(@String, CHARINDEX('SWIFT', @String) - 1))), 'Number: ', '') 

Answer 3

DECLARE @string VARCHAR(MAX) = 'Bank Name: eewweew Chemnitz Bank Account Address: weweweewew Zwickau 12345 dfdfdfdfd fdfdfdfdf. 1-3 Beneficiary Name: Roswitha Haupt-Elster Account Number: TheValueToReturn SWIFT/BIC Code: VVHHH SortCode: sfsffsfsa IBAN: wdffwfafsafsafs'


SELECT LEFT(LTRIM(RTRIM( REPLACE(@string, LEFT(@string, CHARINDEX('Account Number:', @string) + 14), ''))), CHARINDEX(' ', LTRIM(RTRIM( REPLACE(@string, LEFT(@string, CHARINDEX('Account Number:', @string) + 14), '')))))