Solved

MS SQL Update Script

Posted on 2010-08-31
3
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to understand what this mean...types(lstTypes[i].AvailableDatatype) 2 27
Need to find substring in SQL 5 44
sql query 5 44
SQL query 7 22
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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