I recently started learning and using T-SQL for a database integration project I'm doing at work. T-SQL (or Transact-SQL) is Microsoft's extension to SQL that adds some transactional programming features to it. It's used in SQL Server.
I'm slowly getting the hang of it. Mostly I've stumbled with the fact that it's not quite a "real" programming language. So if you think of doing a foreach loop then you better think again and adjust your mindset to using SQL result sets.
For this post I've assembled a few of the most useful ways of searching and replacing text that I've learned.
Searching
You can check if a character exists in a string with the standard LIKE command or by using PATINDEX. Choosing mostly comes down to subtle differences. Notice that PATINDEX returns 1 if the searched character is the first one in the string.
WHERE field_value LIKE '%x%'
WHERE PATINDEX('x', field_value) > 0
The wilcard character % matches any number of characters and _ matches just one character that can be anything. Please note that if you want to find these exact characters with for example PATINDEX then you have to escape them like this: '[_]'.
Replacing
The basic replace is very simple but it can't use any wildcard characters. You can emulate the % wildcard with the following combination of methods. For example if you only want the numbers from "2357|South" then you can do it like this:
-- Basic replace
REPLACE(field_value, 'x', '')
-- Advanced replace
SUBSTRING(field_value, 0, PATINDEX('%|%', field_value))
- First it searches the position of the | character with PATINDEX
- In this case it returns 5
- Then SUBSTRING returns the characters starting from index 0 and ending to the length of the PATINDEX
So that's it! There are some good tutorials on T-SQL string operations here and here.