Geek Answers Handbook

Limit SQL capture group and regex replacement?

I need to convert data from a spreadsheet into insert statements in SQL. I have designed most regular expressions to use the search and replace tool in SSMS, but I am having trouble trying to refer to the 9th parent element in my last replacement.

Here is the original entry:

Blue Doe 12/21/1967 1126 Queens Highway Torrance CA 90802 N 1/1/2012

And this is what I need (for now):

select 'Blue','Doe','19671221','1126 Queens Highway','Torrance','CA','90802','N','20120101'

Due to restrictions on the number of elements allowed in brackets, I have to perform a three-time replacement. This can work in a stored procedure if I can do this work first as a POC.

This is the first matching expression:

^{:w:b:w:b}{:z}/{:z}/{:z:b[0-9A-Za-z:b]+:b:w:b[A-Z]+:b:z:b:w:b}{:z}/{:z}/{:z}

And replacement: \10\2/0\3/\40\5/0\6/\7

This adds zeros in the months and days so that they have at least two characters.

, ( , . ).

:

^{:w:b:w:b}[0-9]*{[0-9]^2}/[0-9]*{[0-9]^2}/{:z}{:b[0-9A-Za-z:b]+:b:w:b[A-Z]+:b:z:b:w:b}[0-9]*{[0-9]^2}/[0-9]*{[0-9]^2}/{:z}

: \1\4\(2,2)\(2,3)\5\8\(2,6)\(2,7)

, SQL, insert.

:

^{:w}:b{:w}:b{:z}:b{[0-9A-Za-z:b]+}:b{:w}:b{[A-Z]+}:b{:z}:b{:w}:b{:z}

: select '\1','\2','\3','\4','\5','\6','\7','\8','\9'

, . - \9 . \9, . \8, N. , , , , 20120101.

, : SSMS/SQL 9 find/replace ? - ? , . POC, sproc .

. -

+5
1

, MS SQL Server Management Studio 2008r2.

Tagged Expression 9, Tagged Expression 8, 9. Microsoft .

, Find/Replace, SSMS, . MS Excel, CONCATENATE, FIND MID, , . , , , .

, , .

: MS SQL Server Management Studio 2008r2, Visual Studio 2005 Visual Studio 2010 , , \9 . , (. Henrique Evaristo) .

, SSMS 9 - .

, , 8 , , . :

^{:w}:b{:w}:b{:z}:b{[0-9A-Za-z:b]+}:b{:w}:b{[A-Z]+}:b{:z}:b{:w}:b:z
select '\1','\2','\3','\4','\5','\6','\7','\8','\0'

:w:b:w:b:z:b[0-9A-Za-z:b]+:b:w:b[A-Z]+:b:z:b:w:b{:z}
\1
+2

More articles:


All Articles