CODEWITHSUNDEEP
sqlsql-serversearch
user2617053
user2617053

Reputation: 333

SQL to look for a particular text

What will be the SQL command for the sample data below:

Table1  
ID|Description  
1 |Logon Failed. User tempUser1 is unknown
2 |Logon Failed. User Carl is unknown  


Expected result to get only the username after the word "User" and before the word "is"  
ID|Description  
1 |tempUser1  
2 |Carl

Thanks,
Ryl

Upvotes: 0

Views: 79

Answers (2)

jpw
jpw

Reputation: 44921

Something like this should work:

SELECT 
  ID, 
  SUBSTRING(
    description, 
    CHARINDEX('User', description) + 5, 
    CHARINDEX('is unknown', description) - CHARINDEX('User', description) - 6
  ) AS Description
FROM table1
ORDER BY ID

The odd formatting of the substring function is just to fit everything on one page here (to remove scrollbars).

Edit:

This should also work , and be more efficient, if the string always starts with Logon Failed. User:

SELECT 
  ID, 
  SUBSTRING(Description, 20, CHARINDEX('is unknown', Description) - 21) AS Description
FROM table1
ORDER BY ID

Upvotes: 1

Sonam
Sonam

Reputation: 3466

You can try using the following query, and replace the text or set the length as per your requirement:

select substring(column_name,patindex('%tempUser%',column_name),9) from Table_Name where column_name like '%tempUser%'

Upvotes: 0

Related Questions