Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

How to Delete Right 3 Characters from a string Expand / Collapse
Author
Message
Posted Monday, July 25, 2011 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:46 AM
Points: 137, Visits: 451
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
Post #1147569
Posted Monday, July 25, 2011 9:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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

Post #1147576
Posted Monday, July 25, 2011 9:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:46 AM
Points: 137, Visits: 451
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
Post #1147595
Posted Monday, July 25, 2011 9:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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!
Post #1147609
Posted Monday, July 25, 2011 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:46 AM
Points: 137, Visits: 451
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
Post #1147631
Posted Monday, July 25, 2011 10:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
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??
Post #1147663
Posted Tuesday, March 25, 2014 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 229, Visits: 693
This helps! Thanks
Post #1554535
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse