Copy substring of one column and copy to another.

Question

This is a bit above my level. But am trying to learn. I don't want to seem like I'm just trying to get my homework done but would appreciate any help pointers.

I am trying to find a substring (postcode) in an address column and once found, copy to the post code column I have the following sql which finds columns that match a postcode pattern.

SELECT Address
  FROM tb_member
 WHERE (Address LIKE '%[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%')

Next I presume I need to find the substring index...
This is where I start to get a little flummoxed - Am I heading in the right direction?

Answer 1

So you know you want to SUBSTRING a value - look at what the function requires to make it work:

1. The string value
2. The starting point of the substring you want to capture
3. The length of the substring you want

In SQL Server/TSQL, PATINDEX will be better for this situation than CHARINDEX to get that starting point of the substring.

I gather you know how long the substring will always be?

Answer 2

PATINDEX will return the substring index for you.