Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
babloo
Starting Member
35 Posts |
 Posted - 2013-05-07 : 10:11:47
|
| Hi I have a question, I have a field in my table called 'LoginName' that field has numberic and alpha numeric values. I want to update everyone who has only numeric values and has less than 6 characters.For example. Currently: 1234I want this: 001234 <-- Making sure it has two leading zeros at the beginning to make it 6 charactersAnother example:Currently: 781I want this: 000781 <-- Adding three zeros to make sure it has total 6 charactersI do not want to modify anyone who has alphabets in their loginnameFor Ex: I do not want following to be modified what so ever.12CAN11AB1234 TEMP99 How can I do that? Any suggestions please.Thanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-07 : 10:18:10
|
| [code]UPDATE YourTable SET loginname = RIGHT('000000'+loginname, 6)WHERE loginname NOT LIKE '%[^0-9]%'[/code] |
 |
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-07 : 10:21:01
|
| Thanks James, loginname NOT LIKE '%[^0-9]%' <-- Not Like or Just LIKE ? I want only those who have numeric usernames.Thanks. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-07 : 10:27:47
|
It should be NOT LIKE. It is applying a double-negative. The caret symbol (^) is a negation. So effectively we are saying "if loginname is not like something that is not a digit then we want to update that row"Before you do the update, try a select and you will see that it works that way:SELECT loginname FROM YourTableWHERE loginname NOT LIKE '%[^0-9]%' |
|
|
|
babloo
Starting Member
35 Posts |
Posted - 2013-05-07 : 10:33:29
|
| Thanks alot James :) |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-07 : 12:19:37
|
| Very welcome - glad to help. |
|
|
|
|
|
|
updating a field