Solved

MS SQL Update Script

Posted on 2010-08-31
3
291 Views
Last Modified: 2012-05-10
I need to update a Alpha Numeric Syntax to include a couple more Digits.

In RegEx the Syntax is
new Regex("^[A-Z]{2,}\\d{2}-\\d{6}$")

I want to do a SQL statement to update this to

new Regex("^[A-Z]{2,}\\d{2}-\\d{9}$")

A couple examples
AA10-123456
BB01-654321

updated to
AA10-000123456
BB01-000654321

If I can just execute a script it would be ideal, but please let me know what you think.
I also would like to search and replace a Text string that might contain several of these numbers.

If I cannot find a nice script I will make a c# function to do it.






0
Comment
Question by:EazyWorks
  • 2
3 Comments
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 500 total points
ID: 33570652
You can use a sql below as a smaple to update your column values

UPDATE  TableName
SET ColumnName = SUBSTRING(ColumnName,1,Charindex('-',ColumnName,1)) + '000'+ RTRIM(SUBSTRING(ColumnName,Charindex('-',ColumnName,1),Len(ColumnName)))
WHERE CHARINDEX('-',ColumnName)>0

0
 

Author Comment

by:EazyWorks
ID: 33571208
This will work, I had to change it to
SET ColumnName = SUBSTRING(ColumnName,1,Charindex('-',ColumnName,1)) + '000'+ RTRIM(SUBSTRING(ColumnName,Charindex('-'+1,ColumnName,1),Len(ColumnName)))

Is there any way I can run a Regular Expression for the where and the Update?
javascript:void(toggleRichText('textBottom',1,'/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26442408.html%3Fcid%3D239%23notices'))
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33571267
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question