How to extract certain part of the string from varchar fields in SQL Server by using T-SQL
Sometimes we need to extract certain part of the string in an SQL Server varchar/text field. The part we are interested may have certain pattern of prefix and postfix. For example, if we want to get the infomation between Person Name: and Address in the following string:
Person Name: Adam Smith Address: 11111 Easy Street, La Jolla, CA
Assuming the field name is x_text, the table name is x_table, then the T-SQL Statement will be:
select substring(x_text, len('Person Name: ') +1 , charindex('Address:', x_text) - len('Person Name: ') - 2) as MyPersonName
from x_table
where left(x_text, len('Person Name: ')) = 'Person Name'
|
