Replace the first charatcer in a String

Question

Replace the first charatcer in a String

hoseam

SSCertifiable

Points: 5533
More actions
September 7, 2017 at 7:22 am

#339691

I want to replace all the values in a Column that starts with 0 to start with 27.
Please help.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

hoseam - Thursday, September 7, 2017 7:22 AM
I want to replace all the values in a Column that starts with 0 to start with 27.
Please help.

Look for the STUFF() function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

hoseam - Thursday, September 7, 2017 7:22 AM
I want to replace all the values in a Column that starts with 0 to start with 27.
Please help.

CREATE TABLE #Test (Dsc VARCHAR(50));

INSERT #Test ( Dsc ) VALUES ('0lzdhksd') ,('abc');

SELECT * FROM #Test t;

UPDATE #Test SET Dsc = STUFF(Dsc, 1, 1, '27') WHERE Dsc LIKE '0%';

SELECT * FROM #Test t;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Avi1 SSCrazy Points: 2323 More actions · Answer 3

please give some sample data and expected result
My understanding is you want to change first character with 27th, below script should be useful

Declare @txt varchar(100) = 'abcdefghijklmnopqrstuvwxyz0123456789'

select @txt, stuff(@txt,1,1,substring(@txt,27,1))

anand08sharma SSC Enthusiast Points: 180 More actions · Answer 4

hoseam - Thursday, September 7, 2017 7:22 AM
I want to replace all the values in a Column that starts with 0 to start with 27.
Please help.

select regexp_replace(column_name,0,27,1,1) from your_table

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

anand08sharma - Thursday, September 7, 2017 10:38 AM
select regexp_replace(column_name,0,27,1,1) from your_table

regexp_replace is not a T-SQL function.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

anand08sharma - Thursday, September 7, 2017 10:38 AM
hoseam - Thursday, September 7, 2017 7:22 AM
I want to replace all the values in a Column that starts with 0 to start with 27.
Please help.
select regexp_replace(column_name,0,27,1,1) from your_table

That function is not part of T-SQL (at least, not yet)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

Phil Parkin - Thursday, September 7, 2017 10:46 AM
anand08sharma - Thursday, September 7, 2017 10:38 AM
select regexp_replace(column_name,0,27,1,1) from your_table
regexp_replace is not a T-SQL function.

It could be a very poorly named scalar udf 😛

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Avi1 - Thursday, September 7, 2017 7:33 AM
please give some sample data and expected result
My understanding is you want to change first character with 27th, below script should be useful
Declare @txt varchar(100) = 'abcdefghijklmnopqrstuvwxyz0123456789'
select @txt, stuff(@txt,1,1,substring(@txt,27,1))

Not the 27th character. The OP want's to replace the first character with the number '27' if and only if the first character is "0".

Phil Parkin 's code in his first post above does that quite nicely.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

audrey.abbey SSC Veteran Points: 211 More actions · Answer 9

Phil Parkin - Thursday, September 7, 2017 10:46 AM
anand08sharma - Thursday, September 7, 2017 10:38 AM
select regexp_replace(column_name,0,27,1,1) from your_table
regexp_replace is not a T-SQL function.

REPLACE is, though.
SELECT REPLACE(Columnname, 0, 27) FROM table WHERE columnname LIKE '0%'

Unfortunately, in SQL Server, REPLACE would replace ALL zeros in the number with a 27, not just the first one.

SELECT '011111010101010', STUFF('011111010101010',1,1,27)

The first argument is the string to act on.
The second argument tells us where to start - First char.
The third tells you how many characters to replace.
The fourth tells you what to replace with.

Update would look something like this:
UPDATE table set column = STUFF(column,1,1,27) WHERE column like '0%'

Phil Parkin SSC Guru Points: 246962 More actions · Answer 10

audrey.abbey - Friday, September 8, 2017 11:02 AM
--
Update would look something like this:
UPDATE table set column = STUFF(column,1,1,27) WHERE column like '0%'

You seem to have regurgitated the query I provided in my first answer on this thread, except you have forgotten to enclose 27 in quotes, forcing the DB engine to perform an unnecessary implicit data conversion.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

audrey.abbey SSC Veteran Points: 211 More actions · Answer 11

Phil Parkin - Friday, September 8, 2017 11:23 AM
audrey.abbey - Friday, September 8, 2017 11:02 AM
--
Update would look something like this:
UPDATE table set column = STUFF(column,1,1,27) WHERE column like '0%'
You seem to have regurgitated the query I provided in my first answer on this thread, except you have forgotten to enclose 27 in quotes, forcing the DB engine to perform an unnecessary implicit data conversion.

You made my whole day, lol. Thank you.

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 12

You can also use replace as below:

UPDATE #Test SET Dsc = REPLACE(dsc,SUBSTRING(dsc,1,1),'27') WHERE Dsc LIKE '0%';

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

crazy_new - Wednesday, September 13, 2017 2:45 AM
You can also use replace as below:
UPDATE #Test SET Dsc = REPLACE(dsc,SUBSTRING(dsc,1,1),'27') WHERE Dsc LIKE '0%';

That keeps the same problem that was described for replace while adding more work.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246962 More actions · Answer 14

Luis Cazares - Wednesday, September 13, 2017 7:56 AM
crazy_new - Wednesday, September 13, 2017 2:45 AM
You can also use replace as below:
UPDATE #Test SET Dsc = REPLACE(dsc,SUBSTRING(dsc,1,1),'27') WHERE Dsc LIKE '0%';
That keeps the same problem that was described for replace while adding more work.

Precisely. Why bother using SUBSTRING to extract character one of a string, when you know (from the WHERE clause) that this character MUST be zero.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin