How to Remove a character in the middle of a stirng

Question

How to Remove a character in the middle of a stirng

Viewing 6 posts - 16 through 20 (of 20 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

JLSSCH (7/14/2009)
The STUFF function should only be executed if the 10th character is an 'H'. Therefore, the STUFF function should be used in combination with a SUBSTRING function and a CASE expression:
DECLARE @input_value VARCHAR(10)
DECLARE @output_value VARCHAR(10)
SET @input_value = 'PQ19188PFHAA'
SET @output_value = (SELECT CASE SUBSTRING(@input_value, 10, 1)
WHEN 'H' THEN STUFF(@input_value, 10, 1, '')
ELSE @input_value
END
)
-- **********************************************
I hope this small modification helps.

Actually, the original post asked how to replace the 10th character, and that is confirmed by the OP in this post.

The sample data provided only had an 'H' in the 10th position.

JLSSCH Ten Centuries Points: 1013 More actions · Answer 2

Lynn:

The original poster's second note says:

"The problem is that this character is not always H, it can be any character. How can I use the Len function to get a fixed character in the string and replace it?"

Therefore, you'd need to use my solution (e.g. STUFF/SUBSTRING/CASE WHEN) in this case.

Thanks,

--Jeff

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Nope. From the original post:

Does any one know how of any function to remove the any characer in the middle of a string?

The other post I referenced reinforces that the character being replaced can be any character, not just an 'H'.

Also reference this post from the OP.

JLSSCH Ten Centuries Points: 1013 More actions · Answer 4

Lynn:

I'm not trying to give you a hard time, but look at his second post that I copied above!!!!

--Jeff

Slick84 SSCertifiable Points: 5602 More actions · Answer 5

Lynn is right... you are reading these posts out of context. Its as simple as 'Stuff' 😎

--
:hehe:

JLSSCH Ten Centuries Points: 1013 More actions · Answer 6

Lynn:

Oops!!!!

I misread his second note that says he wanted to remove a fixed position, whether or not it's an 'H'. My solution would work if he wanted to remove the 10th position only if it was an 'H'.

Sorry.........

--Jeff