Solved

sql string formatting

Posted on 2011-03-24
6
332 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
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:ewangoya
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now