Solved

sql string formatting

Posted on 2011-03-24
6
336 Views
Last Modified: 2012-05-11
how do you script the following in sql

012341234566789

to remove the first '0' and replace with '44'
0
Comment
Question by:amillyard
[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
6 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 35208239
Assuming this is a select statement from a table...


SELECT Number AS NumberBefore, 
CASE LEFT(Number,1)
WHEN '0' THEN '44'+RIGHT(Number,(LEN(NUMBER)-1))
ELSE Number
END AS NumberAfter
FROM PhoneDirectory

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35208242

you can do
select '44' + SUBSTRING('012341234566789', 2, LEN('012341234566789'))

or
select REPLACE('012341234566789', '0', '44')  -- but this will replace all occurrences of 0
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35208247
use replace('012341234566789','0','44')

Update Table1 Set Col1 = replace(Col1, '0', '44');
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:amillyard
ID: 35208732
EvilPostIt: do u have that as an update / set statement?
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 35208743
UPDATE PhoneDirectory
SET Number='44'+RIGHT(Number,(LEN(NUMBER)-1))
WHERE LEFT(Number,1)='0'

Open in new window

0
 

Author Closing Comment

by:amillyard
ID: 35209417
spot on - thank you.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Job - date manual 1 42
Database-Scoped Permissions 2 17
Database Mail Profiles 1 21
T-SQL: Need Group By to use "fuzzy logic"?? 3 21
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 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