Inserting a value in an ordered string
-
I am looking for a way to insert a value into a string while preserving the order of the string. For example, given the string '1348' I need to insert the value 7 such that the resulting string is '13478'. Is there a more efficient/elegant way of doing this other than testing the length of the string and byte values and then using if clauses to handle each substring scenario?
Many thanks in advance for your help!
S Ely
-
Heh...bwa-heh... BWAAA-HAAA!!! Hey, Sergei... STUFF it will ya? :P:D:)
Oh dear... couldn't resist the pun. Seriously, though... that's the answer... see here...
DECLARE @SomeExistingString VARCHAR(100),@SomeStringToInsert VARCHAR(100)
SELECT @SomeExistingString = '1348',
@SomeStringToInsert = '7'
SELECT [font="Arial Black"]STUFF[/font](@SomeExistingString,4,0,@SomeStringToInsert)
--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) -
Thanks Jeff. I neglected to mention that the insert position is variable, though. The field is char(9) with the values restricted to integers 1-9 so the insertion could be in byte positions 1-7. So I guess the real question is what is the most efficient way to determine the position it should go in?
Sergei
-
Ah... that's a slightly different story. Am I to assume that you want the digit to be inserted in the correct ascending numerical order... in other words, the 7 went were it did because it's numerically between the 4 and the 8? That can be done pretty easily but lemme know if that's the actual requirement, please.
Also, I'm always interested in the "why"... why is it that you have to do such a thing? What are the business rules behind this? Thanks.
--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) -
Your assumption about the numerical order is correct. The existence of a number in the field refers to a specific checkbox (of which there are 10)in the front end app. These checkboxes signify logical yes/no and represent diferent marketing mailing purposes. Not being the designer of the app, I'm not sure why they chose to store the data this way vs. separate bit fields.
-
Thanks for taking the time to explain. Even though it's for a necessarily RBAR action for a GUI and there's probably no need to handle the proverbial "millions of rows" for this one, I'll try to make it scalable to some extent and still try to make it easy to use... there's a couple of things I've gotta try. I'll be back soon.
--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) -
Ok... that was fun... here's a function to do what you want. The function will make it easy and consistent on folks to use and I tried to build some speed into it using some short circuiting, some short cuts using a derived table like you would a CTE (yep... this code works in SQL Server 2000, too!), and my dearest coding friend, the Tally table. Before you can build the function, you'll need a Tally table and here's where you can get one...
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/font][/url]
The article explains why the Tally table is so powerful and how it makes otherwise difficult code a real breeze to think about and write.
Here's the function to do your ordered insertion into a string... I did make it so it will only insert 1 character at a time... if ya wanna know how it does what it does, just read the embedded comments
CREATE FUNCTION dbo.OrderedCharacterInsert/**********************************************************************************************************************
Purpose:
This function accepts an existing order string and inserts a charcter in the proper order.
Note... spaces are not allowed in either string.
Example and usage:
SELECT dbo.OrderedCharacterInsert('1348','0') UNION ALL --Returns 01348
SELECT dbo.OrderedCharacterInsert('1348','1') UNION ALL --Returns 1348 because the "1" already existed
SELECT dbo.OrderedCharacterInsert('1348','7') UNION ALL --Returns 13478
SELECT dbo.OrderedCharacterInsert('1348','9') --Returns 13489
Revision History:
Rev 00 - 09 Jan 2009 - Jeff Moden - Initial creation and test
Written for: http://www.sqlservercentral.com/Forums/Topic633926-338-1.aspx?
**********************************************************************************************************************/
(
@ExistingString VARCHAR(256),
@CharToInsert CHAR(1)
)
RETURNS VARCHAR(256)
AS
BEGIN
RETURN (
SELECT CASE -- If character isn't already present, then insert it
WHEN CHARINDEX(@CharToInsert,posit.ExistingString) = 0
THEN LTRIM(RTRIM(STUFF(posit.ExistingString,posit.InsertHere,0,@CharToInsert)))
ELSE @ExistingString
END
FROM (--==== Determines character to insert at and modified string to insert on.
-- The TOP 1 short circuits the "pseudo cursor" created by the
-- Tally table for extra speed. The -t.N does it all backwards so
-- we don't have to ORDER BY DESC... again, for extra speed.
SELECT TOP 1 (LenExistingString -t.N +1) AS InsertHere,ExistingString
FROM dbo.Tally t,
(--==== Adds spaces to string to insert on and gets its new length.
-- Works like a CTE so we don't have to add the spaces elsewhere in code.
SELECT ' '+@ExistingString+' ' AS ExistingString,
LEN(@ExistingString)+2 AS LenExistingString
) es
WHERE t.N <= LenExistingString
AND SUBSTRING(es.ExistingString,(LenExistingString -t.N),1) < @CharToInsert
ORDER BY t.N --Not actually used by execution plan, but makes nay sayers breath easier.
--And, yes, it still works in 2k5 ;-)
) posit
) --End of return
END
... and I'm thinking that you can probably figure out how to use it from there. Thanks for the interesting problem. 😀
--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) -
Wow. I'll take a look at this over the weekend and let you know how it goes. Thanks again!
Sergei Ely
-
Here's another way.
SELECT CASE WHEN CHARINDEX(@CharToInsert,@ExistingString) > 0THEN @ExistingString
ELSE RTRIM(STUFF(@ExistingString+' ',PATINDEX('%['+@CharToInsert+'-9]%',@ExistingString+'9'),0,@CharToInsert))
END
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
John,
The code worked perfectly. 😀 Thanks again.
Sergei
-
Mark,
Thanks for the input. The solution requires placement of the integer within the ordered string at variable positions. For example, the existing string could be a single integer ie '4' with the desired result being '47', or '12468' => '124678', or ' ' => '7'. Sorry if I didn't make that clear in my post.
S Ely
-
Sergei Ely (1/12/2009)
John,The code worked perfectly. 😀 Thanks again.
Sergei
Heh... actually, it's "Jeff" but I appreciate the feedback anyway. Thanks, Sergei. 🙂
--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) -
Sergei Ely (1/12/2009)
Mark,Thanks for the input. The solution requires placement of the integer within the ordered string at variable positions. For example, the existing string could be a single integer ie '4' with the desired result being '47', or '12468' => '124678', or ' ' => '7'. Sorry if I didn't make that clear in my post.
S Ely
which is what my solution does....
CREATE FUNCTION dbo.AnotherOrderedCharacterInsert(
@ExistingString VARCHAR(256),
@CharToInsert CHAR(1)
)
RETURNS VARCHAR(256)
AS
BEGIN
RETURN (
SELECT CASE WHEN CHARINDEX(@CharToInsert,@ExistingString) > 0
THEN @ExistingString
ELSE RTRIM(STUFF(@ExistingString+' ',PATINDEX('%['+@CharToInsert+'-9]%',@ExistingString+'9'),0,@CharToInsert))
END
)
END
GO
SELECT dbo.AnotherOrderedCharacterInsert('4','7') UNION ALL --Returns 47
SELECT dbo.AnotherOrderedCharacterInsert('12468','7') UNION ALL --Returns 124678
SELECT dbo.AnotherOrderedCharacterInsert('','7') UNION ALL --Returns 7
SELECT dbo.AnotherOrderedCharacterInsert('1348','0') UNION ALL --Returns 01348
SELECT dbo.AnotherOrderedCharacterInsert('1348','1') UNION ALL --Returns 1348 because the "1" already existed
SELECT dbo.AnotherOrderedCharacterInsert('1348','7') UNION ALL --Returns 13478
SELECT dbo.AnotherOrderedCharacterInsert('1348','9') --Returns 13489
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Mark, if you do a final replace of ")" with "& # 0 4 1 ;" (without the spaces), then your code wil show correctly without all those bloody smiley faces. You can turn them off as part of your profile, but I prefer to have them on because I'm frequently taken the wrong way without them. Just don't want the buggers in my code and thought I'd share a suppression method with you.
--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) -
Mark,
VERY clever code... I obviously never thought of using PatIndex and a variable for the first character of a range... And, it's about twice as fast as the Tally table method.
The very cool part is that it's twice as fast as that if you take it out of the UDF and use the code inline.
--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)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply