Solved

MS SQL Update Script

Posted on 2010-08-31
3
292 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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 …

829 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