Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Delete Right 3 Characters from a string


How to Delete Right 3 Characters from a string

Author
Message
Mad Myche
Mad Myche
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 518
Ninja's_RGR'us (7/24/2011)
All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.


As long as there are a minimum of 3 characters in the field

Director of Transmogrification Services
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Mad Myche (7/25/2011)
Ninja's_RGR'us (7/24/2011)
All we said is that it can delete the wrong data with replace. Left is clear as day. Whack the last 3 characters and most importantly, it cannot fail.


As long as there are a minimum of 3 characters in the field


Pick your poison, you always have to be careful with that type of manipulations. Here are the "fully correct" options.


DECLARE @a TABLE (col VARCHAR(10))

INSERT INTO @a (col) VALUES ('')
INSERT INTO @a (col) VALUES (NULL)
INSERT INTO @a (col) VALUES ('123')
INSERT INTO @a (col) VALUES ('123123123')
INSERT INTO @a (col) VALUES ('123456789')

SELECT col
, REPLACE(col, RIGHT(col, 3), '') AS [Replace = wrong output]
, CASE WHEN col IS NOT NULL THEN ISNULL(STUFF(col, DATALENGTH(col) - 3 + 1, 3,''), '') ELSE NULL END AS Stuf
, CASE WHEN DATALENGTH(col) >= 3 THEN LEFT(col, DATALENGTH(col) - 3) ELSE CASE WHEN col IS NULL THEN col ELSE '' END END AS Lft
FROM @a


Mad Myche
Mad Myche
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 518
I definitely agree with that. I have worked with too many people who design and finalize based on correct data being present, and have very little or no error prevention/handling.

Looking at the original ages old post, I noticed that the data appears to be a 3 element asterisk delineated string. Depending on the needs, removal of the third element could be a better option; either in the data itself or at presentation/usage time

Director of Transmogrification Services
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Mad Myche (7/25/2011)

Looking at the original ages old post, I noticed that the data appears to be a 3 element asterisk delineated string. Depending on the needs, removal of the third element could be a better option; either in the data itself or at presentation/usage time



I'd agree with that too. But we can only go with the specs given to us! w00t
Mad Myche
Mad Myche
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 518
Agree with that as well I do.

Often what they say they want is not really what they want, or is incomplete. I will always let the person giving the specs know if my view of the big picture strays from their possible tunnel vision. (and say "I told you so" when they come back months later asking me for what I already planned on)

Director of Transmogrification Services
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
Mad Myche (7/25/2011)
Agree with that as well I do.

Often what they say they want is not really what they want, or is incomplete. I will always let the person giving the specs know if my view of the big picture strays from their possible tunnel vision. (and say "I told you so" when they come back months later asking me for what I already planned on)


Care to share specific stories?? Hehe
sharonsql2013
sharonsql2013
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 1220
This helps! Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search