?
Solved

MS SQL Update Script

Posted on 2010-08-31
3
Medium Priority
?
296 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 2000 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 2000 total points
ID: 33571267
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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