April 16, 2018 at 8:57 am
Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)
Select @STR =
'<20170101><20171231><20170101>XPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Select @st = SUBSTRING(@str,2,8)
Select @ed = SUBSTRING(@str, 12, 8 )
Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )
--Show me how I could replace the X with a P in positions 32-38
--Rules: You can only replce an X with a P when the string has a X in that position.
--Otherwise do not replace.
April 16, 2018 at 9:07 am
Sounds very much like a homework question. What have you tried?
One approach would be to use a splitter function to split that part the string into its individual characters, do the replace, then reassemble.
John
April 16, 2018 at 9:11 am
mw_sql_developer - Monday, April 16, 2018 8:57 AMDeclare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)
Select @STR =
'<20170101><20171231><20170101>XPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'Select @st = SUBSTRING(@str,2,8)
Select @ed = SUBSTRING(@str, 12, 8 )
Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )--Show me how I could replace the X with a P in positions 32-38
--Rules: You can only replce an X with a P when the string has a X in that position.
--Otherwise do not replace.
Your example is a bit challenging because all the characters are the same. But this isn't really all that difficult. Use substring to isolate the subset you are interested in. Then use a basic replace to change X to P. Then use stuff to shove the new value back in. This can be done in a single statement or break it out.
Something along these lines.
select SectionOfInterest = substring(@str, 32, 7)
, ReplacedValues = replace(substring(@str, 32, 7), 'X', 'P')
, StuffedWithReplacedValues = stuff(@str, 32, 7, replace(substring(@str, 32, 7), 'X', 'P'))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2018 at 9:46 am
This is what I did...
I was hoping someone could come up with something without using LOOPS.
I did mention that replacements must be done only of the character is a "X" in that position.
I did not see you ( in the above response ) checking before you do a replace
Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)
Select @STR =
'<20170101><20171231><20170101>XPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
Select @st = SUBSTRING(@str,2,8)
Select @ed = SUBSTRING(@str, 12, 8 )
Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )
--Show me how I could replace the X with a P in positions 32-38
--Rules: You can only replce an X with a P when the string has a X in that position.
--Otherwise do not replace.
Declare @overlap as int, @i as int
Select @i=32;
Select @overlap = 0;
While( @i <= 38 )
Begin
If( Substring(@str, @i , 1 ) = 'X' )
Begin
Select @STR = LEFT(@str, @i-1 ) + 'P' + SUBSTRING( @STR, @i+1, LEN(@str) )
End
else
Select @overlap = @overlap+1
Select @i=@i+1;
End
Select @STR ,@overlap
April 16, 2018 at 9:55 am
mw_sql_developer - Monday, April 16, 2018 9:46 AMThis is what I did...
I was hoping someone could come up with something without using LOOPS.
I did mention that replacements must be done only of the character is a "X" in that position.
I did not see you ( in the above response ) checking before you do a replace
Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)Select @STR =
'<20170101><20171231><20170101>XPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'Select @st = SUBSTRING(@str,2,8)
Select @ed = SUBSTRING(@str, 12, 8 )
Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )--Show me how I could replace the X with a P in positions 32-38
--Rules: You can only replce an X with a P when the string has a X in that position.
--Otherwise do not replace.Declare @overlap as int, @i as int
Select @i=32;
Select @overlap = 0;While( @i <= 38 )
Begin
If( Substring(@str, @i , 1 ) = 'X' )
Begin
Select @STR = LEFT(@str, @i-1 ) + 'P' + SUBSTRING( @STR, @i+1, LEN(@str) )
End
else
Select @overlap = @overlap+1
Select @i=@i+1;
EndSelect @STR ,@overlap
I don't get what you mean about checking. If you replace a P with an X it is only going to put in a P where there was an X. That is what REPLACE does. It replaces one set of characters with another. And loops should be avoided like the plague for this. There is absolutely no reason to loop here. The code I posted should do exactly what you asked it to do. If it doesn't, then please explain what doesn't work with it and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2018 at 10:27 am
mw_sql_developer - Monday, April 16, 2018 9:46 AMThis is what I did...
I was hoping someone could come up with something without using LOOPS.
I did mention that replacements must be done only of the character is a "X" in that position.
I did not see you ( in the above response ) checking before you do a replace
hmmmm, what do you mean? Sean has already posted a perfect solution to the problem you posted, is there something else?
😎
April 16, 2018 at 1:41 pm
Here might be a better way to check how Sean's code works. I use tsqlt for repeatable testing. You could certainly download the framework, which would allow you to test this:
Table Code:
CREATE TABLE DatesAndCharacters
( rowid INT IDENTITY(1,1) NOT null
, StringVar VARCHAR(2000)
, CONSTRAINT DatesandCharactersPK PRIMARY KEY (rowid)
)
;
Proc to run Sean's Code:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.ReplaceXwithP
AS
BEGIN
UPDATE dbo.DatesAndCharacters
SET StringVar = stuff(StringVar, 32, 7, replace(substring(StringVar, 32, 7), 'X', 'P'))
END
GO
Test that will verify this code works. You can change the input set and the expected values, or add new rows as you build cases.
--SET QUOTED_IDENTIFIER ON|OFF
--SET ANSI_NULLS ON|OFF
--GO
EXEC tsqlt.NewTestClass @ClassName = N'StringTests'
go
CREATE PROCEDURE StringTests.[test partial string replacement]
AS
-- Assemble
EXEC tsqlt.FakeTable @TableName = N'DatesAndCharacters' ,
@SchemaName = N'dbo';
INSERT dbo.DatesAndCharacters
( RowID,
StringVar
)
VALUES
(1, '<20170101><20171231><20170101>XPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(2, '<20170101><20171231><20170101>PPPXPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(3, '<20170101><20171231><20170101>XPXXPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(4, '<20170101><20171231><20170101>XPPRCXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(5, '<20170101><20171231><20170101>PPPXPXXXXCPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' )
CREATE TABLE #Expected
( rowid INT NOT null
, StringVar VARCHAR(2000)
, CONSTRAINT DatesandCharactersPK PRIMARY KEY (rowid)
)
INSERT #Expected
( rowid,
StringVar
)
VALUES
(1, '<20170101><20171231><20170101>XPPPPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(2, '<20170101><20171231><20170101>PPPPPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(3, '<20170101><20171231><20170101>XPPPPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(4, '<20170101><20171231><20170101>XPPRCPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ),
(5, '<20170101><20171231><20170101>PPPPPPPPXCPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' )
-- Act
EXEC ReplaceXwithP;
-- Assert
EXEC tsqlt.AssertEqualsTable @Expected = N'#Expected' ,
@Actual = N'DatesandCharacters' ,
@Message = N'Wrong replacement' ,
@FailMsg = N'The procedure didn''t work'
GO
EXEC tsqlt.run 'StringTests.[test partial string replacement]'
April 16, 2018 at 2:39 pm
Sean Lange - Monday, April 16, 2018 9:55 AMmw_sql_developer - Monday, April 16, 2018 9:46 AMThis is what I did...
I was hoping someone could come up with something without using LOOPS.
I did mention that replacements must be done only of the character is a "X" in that position.
I did not see you ( in the above response ) checking before you do a replace
Declare @STR as varchar(1000), @st varchar(8), @ed VARCHAR(8), @EarliestPrescriptionDt varchar(8)Select @STR =
'<20170101><20171231><20170101>XPPPPXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'Select @st = SUBSTRING(@str,2,8)
Select @ed = SUBSTRING(@str, 12, 8 )
Select @EarliestPrescriptionDt = SUBSTRING(@str, 22, 8 )--Show me how I could replace the X with a P in positions 32-38
--Rules: You can only replce an X with a P when the string has a X in that position.
--Otherwise do not replace.Declare @overlap as int, @i as int
Select @i=32;
Select @overlap = 0;While( @i <= 38 )
Begin
If( Substring(@str, @i , 1 ) = 'X' )
Begin
Select @STR = LEFT(@str, @i-1 ) + 'P' + SUBSTRING( @STR, @i+1, LEN(@str) )
End
else
Select @overlap = @overlap+1
Select @i=@i+1;
EndSelect @STR ,@overlap
I don't get what you mean about checking. If you replace a P with an X it is only going to put in a P where there was an X. That is what REPLACE does. It replaces one set of characters with another. And loops should be avoided like the plague for this. There is absolutely no reason to loop here. The code I posted should do exactly what you asked it to do. If it doesn't, then please explain what doesn't work with it and we can help.
Sorry my mistake.. What was i thinking ? You are right. Replace only replaces if it was a X.
forgot to mention something. I do have to keep track on the count as well. meaning how many X chars were replaced with P. So I need the loop.
Ahh! I should have mentioned that at the very begining
April 16, 2018 at 3:25 pm
You don't need the loop if you combine Sean's code with the answer you got from your other post.
https://www.sqlservercentral.com/Forums/1932209/How-do-I-count-the-number-of-occurrences-the-character-P-in-the-string
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
April 16, 2018 at 3:33 pm
Yep, grab the substring, compare length with length of missing Xs.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply