CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
RobinВricks
RobinВricks

Reputation: 103

SQL Server : find a substring after the keyword

I'm trying to find the password on the line, I need to find a substring after the keyword "password".

The SQL Server database table contains these sample rows:

hello do you have any problems? Password: Qq111222 good luck  tel: 9-189309, email: [email protected]
hello do you have any problems? Password: Aw654371 good luck  tel: 9-189309, email: [email protected]
hello do you have any problems? Password: Zd354321 good luck  tel: 9-189309, email: [email protected]
hello do you have any problems? Password: temporary password good luck  tel: 9-189309, email: [email protected]
hello do you have any problems? Password: temporary password good luck  tel: 9-189309, email: [email protected]

I'm looking for a way to find 10 characters after of the word Password, substring contains numbers and letters, is there a way to write this in SQL Server?

I using the following but got a lot of unsuitable rows:

select fields
from table
where fields like '%Password%[a-z,A-Z]%[0-9]%'

Upvotes: 0

Views: 2716

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use charindex() :

select *, substring(fields, charindex('password', fields)+9, 10)
from table t

However, this may fail if your string doesn't have password word so, you can add prevention with charindex() or include where clause :

select *, substring(fields, charindex('password', fields + 'password')+9, 10)
from table t;

Upvotes: 3

Thom A
Thom A

Reputation: 96036

Assuming you want the space on either side:

DECLARE @S varchar(8000);
SET @S = 'hello do you have any problems? Password: Qq111222 good luck  tel: 9-189309, email: [email protected]';
SELECT SUBSTRING(@S,PATINDEX('%Password:%',@S) + LEN('Password:'),10);

Upvotes: 0

Related Questions