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

Select Command while dropping first 2 characters in the column Expand / Collapse
Author
Message
Posted Tuesday, March 24, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 12, 2010 3:26 PM
Points: 7, Visits: 55
I am looking for a solution to a problem. I can't quite find the command I am looking for. I want to select from a column where the items in the column are like

ks105
ks103
ks106
and so on

I want my results to produce the
105
103
106
and so on.

I want my select command to drop the first 2 characters in that column when displaying the results. I do not want to modify the information in that table just want my select to display differently.

Thanks in advance

Richtoss
Post #682549
Posted Tuesday, March 24, 2009 10:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 41,565, Visits: 34,488
Lookup the RIGHT function in Books Online. To get the results you want, you may need to combine it with LEN to get all the string except the first two characters.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #682554
Posted Tuesday, March 24, 2009 11:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
You can use the STUFF or SUBSTRING functions as well as the RIGHT function. The SUBSTRING function version is likely to be the fastest, followed by the RIGHT version then the STUFF version.

DECLARE @value varchar(20)
SELECT @value = 'ks105'

SELECT STUFF(@value, 1, 2, '') AS [Stuff],
SUBSTRING(@value, 3, 18) AS [Substring],
RIGHT(@value, LEN(@value) - 2) AS [Right]

Post #682654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse