July 14, 2009 at 10:01 am
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.
July 14, 2009 at 10:54 am
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
July 14, 2009 at 11:05 am
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.
July 14, 2009 at 11:23 am
Lynn:
I'm not trying to give you a hard time, but look at his second post that I copied above!!!!
--Jeff
July 14, 2009 at 11:25 am
Lynn is right... you are reading these posts out of context. Its as simple as 'Stuff' 😎
--
:hehe:
July 14, 2009 at 11:26 am
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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply