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

Your question is clear and some people just do negative markings for fun i believe, i have written to moderator yesterday. We should all give comments for down voting, that is professional integrity — Ven, Apr 05 '18 at 12:02
I agree, that way I can see how I SHOULD ask the question rather. — Etienne, Apr 05 '18 at 12:04
The downvote wasn't me, but I think it's because you don't show any effort to solve it on your own. — MatSnow, Apr 05 '18 at 12:05
Been trying to do it myself for the last hour, did a lot of Google'ing and nothing comes close my problem. — Etienne, Apr 05 '18 at 12:07
Can you please separate expected output from the question (string) please — Ven, Apr 05 '18 at 12:08
@Etienne How should one know if you don't add what you've tried to your question? — MatSnow, Apr 05 '18 at 12:08

score 4 · Answer 1 · answered Apr 05 '18 at 12:07

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;

score 3 · Accepted Answer · answered Apr 05 '18 at 12:17

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: ', '')

score 1 · Answer 3 · answered Apr 05 '18 at 12:08

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), '')))))

Return Word in String - SQL

3 Answers3