Insert character question

Question

Insert character question

hansontd

SSC Enthusiast

Points: 169
More actions
January 8, 2010 at 2:43 pm

#218102

I need to insert a period in a varchar column always 3 "places" over from the right side of the value for example XXXX.XX
Hoping someone can clarify for me the best way (if any) to accomplish this?
Thanks in advance!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ray K SSC-Dedicated Points: 31148 More actions · Answer 1

hansontd (1/8/2010)
I need to insert a period in a varchar column always 3 "places" over from the right side of the value for example XXXX.XX

I suspect there might be a better way to do this, but off the top of my head, try:update

set [column] = left([column], len([column]) - 2 + '.' + right([column], 2)

Obviously, replace

and [column] with your table and column names.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

hansontd SSC Enthusiast Points: 169 More actions · Answer 2

hansontd

SSC Enthusiast

Points: 169

January 8, 2010 at 3:41 pm

#1101240

Great! Thank you very much for your help!!

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Here it is using STUFF.

DECLARE @Var varchar(10)

SET @Var = 'XXXXXXXX'

SELECT STUFF(@Var, LEN(@Var) - 2, 0, '.')

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ray K SSC-Dedicated Points: 31148 More actions · Answer 4

John Rowan (1/8/2010)
Here it is using STUFF.
DECLARE @Var varchar(10)
SET @Var = 'XXXXXXXX'
SELECT STUFF(@Var, LEN(@Var) - 2, 0, '.')

Ah, yes. I defer to those who have more SQL experience than I do! 🙂

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

hansontd SSC Enthusiast Points: 169 More actions · Answer 5

Thank you both very much for your help with my issue!! 😀

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Ray K (1/8/2010)
John Rowan (1/8/2010)
Here it is using STUFF.
DECLARE @Var varchar(10)
SET @Var = 'XXXXXXXX'
SELECT STUFF(@Var, LEN(@Var) - 2, 0, '.')
Ah, yes. I defer to those who have more SQL experience than I do! 🙂

I don't see STUFF used as often as it can be. It is a seemingly little known function that can be very handy with string manipulations. It can work similar to REPLACE and overwrite a section of the string, or insert new values into the string....leading to the word STUFF as in like a turkey!

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

hansontd SSC Enthusiast Points: 169 More actions · Answer 7

I've used the STUFF function numerous times in the past but just couldn't wrap my mind around how to work it in this case....I've always dealth with strings and worked with stuff from the left side of the string whereas in this case I needed to work back from the right...

Thanks again to you both for taking the time to jump in and lend a hand!!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

hansontd (1/9/2010)
I've used the STUFF function numerous times in the past but just couldn't wrap my mind around how to work it in this case....I've always dealth with strings and worked with stuff from the left side of the string whereas in this case I needed to work back from the right...
Thanks again to you both for taking the time to jump in and lend a hand!!

Try REVERSE() 😎

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

hansontd SSC Enthusiast Points: 169 More actions · Answer 9

I had tried utilzing REVERSE() with stuff but for whatever reason I guess I wasn't utilizing it correctly as I never got teh results I was after...but thank for the suggestion....

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

hansontd (1/10/2010)
I had tried utilzing REVERSE() with stuff but for whatever reason I guess I wasn't utilizing it correctly as I never got teh results I was after...but thank for the suggestion....

SELECT REVERSE(STUFF(REVERSE(@Var), 4, 0, '.'))

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]