Basic pattern recognition in SQL Server

Question

I currently have my data stored in this format :

Word1 - Word2 - Number.I.Need.To.Extract.Separated.By.Periods - Word3 - Word4

What I would like to do is write an update statement that will set a column which I have just created to Number.I.Need.To.Extract.Separated.By.Periods.

What is my best way to do this, or work like this?

I have tried to write it as follows :

UPDATE Table1
SET NewColumn = 
               (SELECT SUBSTRING(Column, 19, 14)
                FROM Table1)

Which results in :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

How can I fix this ?

http://stackoverflow.com/search?q=subquery+returns+more+than+one+row&submit=search — Ben, Jun 26 '12 at 13:00
possible duplicate of [sql-server-2005: How to perform a split on a pipe delimited string variable?](http://stackoverflow.com/questions/7391473/sql-server-2005-how-to-perform-a-split-on-a-pipe-delimited-string-variable) — Ben, Jun 26 '12 at 13:02

score 1 · Accepted Answer · answered Jun 26 '12 at 09:47

1

You don't need the subquery as the update occurs "inside" the same row.

Doesn't this work?

UPDATE Table1 SET NewColumn = SUBSTRING(Column, 19, 14)

answered Jun 26 '12 at 09:47

CyberDude

8,541
5
29
47

score 0 · Answer 2 · answered Jun 26 '12 at 12:52

When it comes to text analysis and string manipulations , regular expressions provide a much more efficient and elegant solution than TSQL string functions.

You can write a CLR User-Defined Function which extracts a pattern matching string.

UPDATE Table1 SET NewColumn = fn_ExtractPatternMatchingString(Column,pattern)

Basic pattern recognition in SQL Server

2 Answers2