May 15, 2012 at 3:36 pm
What would the syntax be for trimming a string on both ends? I want to remove the first two and last to characters in a string. Any ideas?
May 15, 2012 at 3:42 pm
bpowers (5/15/2012)
What would the syntax be for trimming a string on both ends? I want to remove the first two and last to characters in a string. Any ideas?
Trimming a string usually refers to whitespace and is normally done with RTRIM() and LTRIM() in combination. However, that's not what you need here. 🙂
What you want is SUBSTRING() and LEN():
DECLARE @blah VARCHAR(50)
SET @blah = '12kjdfa;lksdjskl;ajfl;kasdjfl;sajlkfjasdfsazzz34'
SELECT SUBSTRING( @blah, 3, len( @blah) -4)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 15, 2012 at 3:50 pm
The problem I am trying to code out, is that the length of the string will grow over time. However, as it grows I want strip off the first two and last two characters. Is this possible?
May 15, 2012 at 3:56 pm
Elaborate. You really haven't given us enough information if what Kraig provided isn't what you are trying to do.
May 15, 2012 at 3:58 pm
bpowers (5/15/2012)
The problem I am trying to code out, is that the length of the string will grow over time. However, as it grows I want strip off the first two and last two characters. Is this possible?
What Craig showed you will work no matter the length of the string :-).
_________________________________
seth delconte
http://sqlkeys.com
May 15, 2012 at 4:02 pm
bpowers (5/15/2012)
The problem I am trying to code out, is that the length of the string will grow over time. However, as it grows I want strip off the first two and last two characters. Is this possible?
As Lynn mentioned, the code I posted above will always strip off the front and back two characters, using the length to determine how long it should keep the central string component.
Play with the declared component of the string, and you'll see it works for any length of string that has at least 5 characters.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 15, 2012 at 4:07 pm
Worked perfect, just had to adjust the -4. Thanks Kraig!
Can you explain to me how this is working? I assume the 3 is where it will start in the string, but I am not quite sure about the last part. Any further explanation in helping me understand what is going on will be greatly appreciated.
Thanks.
May 15, 2012 at 4:13 pm
bpowers (5/15/2012)
Worked perfect, just had to adjust the -4. Thanks Kraig!Can you explain to me how this is working? I assume the 3 is where it will start in the string, but I am not quite sure about the last part. Any further explanation in helping me understand what is going on will be greatly appreciated.
Thanks.
Errr, if you want two off the front and two off the end, you want -4 from the length. If that's not doing what you need copy/paste a text sample/result and that'll help me figure out what I'm not understanding from your explanation.
Alright, first, lookup the len and substring commands in BOL, that'll help. LEN gets you the length of the string. Substring takes out a piece of the string.
You're correct, the 3 tells it which character to start on, so it'll always start on the 3rd character. The last parameter is the length to take, so if you took Substring( 'abcd', 2,1) you'd end up with 'b'. 2,2 = 'bc', etc.
By taking the existing LEN() - 4 you're truncating the last two characters because you're skipping the first two.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 16, 2012 at 6:46 am
Got it. Thanks for all your help.
May 16, 2012 at 8:13 am
select replace(replace(@blah,LEFT(@blah,2),''),RIGHT(@blah,2),'')
select left(RIGHT(@blah,len(@blah)-2),len(@blah)-4)
select replace(right(REPLACE(left(@blah,2),'')),2),'')
select SUBSTRING(@blah,3,LEN(@blah)-4)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply