3

Query:

select * from aa;

Output:

ID  FirstName   LastName    City
-----------------------------------
29  Abcrdrr     hai         chennai
67  adf         adsd        adsf
1   John        vinoth      CITY

I want to display the lastname column without last character. I am using following queries:

Declare @name as varchar(100);

select @name = city from aa
Select @name, left(@name, len(@name)-1) as AfterRemoveLastCharacter

But output will come first row only

8

"The first shall be last, and the last shall be first"

REVERSE(STUFF ( REVERSE(lastname), 1, 1, ''))
  • Voting up for the comment alone. – John Eisbrener Jun 16 '14 at 14:36
1

Quick and dirty, probably good enough:

SELECT
    ID
    ,FirstName
    ,CASE WHEN LEN(LEN(RTRIM(LastName)))>1 THEN LEFT(LastName,LEN(RTRIM(LastName))-1) ELSE '' END AS LastName
    ,City 
FROM [table]
  • Suppose i run this query Last Name Column Display Empty values – Vinoth _S Jun 10 '14 at 6:57
  • Sorry This query working good. To Change (>1) into ( >=1) – Vinoth _S Jun 10 '14 at 7:08
  • @Vinoth_S, doing LEFT(field,0) throws an exception - which is 1-1 - so you'll want to consider that. – Mark Henderson Jun 10 '14 at 8:07
0

I found this can make more sense:

select 
    txt, 
    left(txt, abs(len(txt + ',') - 2))
from 
    t;

[SQL Fiddle Demo]

Provides these without any error:
* Last character removed for a normal string.
* Last character removed for a space ended string.
* Last character removed for a single space character string.
* Empty-string results in empty-string.
* null results in null
[SQL Fiddle Demo]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.