CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rameez Javed
Rameez Javed

Reputation: 63

How to remove last comma from csv file and add string in to it in SQL server 2012

I have csv file in SQL 

@code varchar(10) = 'pc-shr,pqr,qzcx,rtde,rz-fg,kj-hl,jk_lm'

I tried

Convert(Varchar(10), @code) + CASE WHEN len(@code) >10 THEN '..etc' Else  '' END [code]

but I want like this means to find last comma in a string and after that add ..etc it should look like this

'pc-shr,pqr, ..etc'

Upvotes: 0

Views: 369

Answers (3)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

declare @code varchar(10) = 'pc-shr,pqr,qzcx,rtde,rz-fg,kj-hl,jk_lm'
select @code
--pc-shr,pqr

SELECT SUBSTRING(@code, 1, LEN(@code)-CHARINDEX (',' ,REVERSE(@code))) + '..etc'
--pc-shr..etc

Replacing right part of string where is last comma, if you want results like in your example increase size - varchar(11)

EDIT: you want this?

SELECT SUBSTRING(@code, 1, LEN(@code)-CHARINDEX (',' ,REVERSE(@code))+1) + '..etc'
--pc-shr,..etc

Last comma in a string @code varchar(10) is this pc-shr*,*pqr so I add ..etc to it

SELECT SUBSTRING(@code, 1, LEN(@code)-CHARINDEX (',' ,REVERSE(@code))+1) + '..etc ' 
   +  SUBSTRING(@code, LEN(@code)-CHARINDEX (',' ,REVERSE(@code))+2,LEN(@code)) 
--pc-shr,..etc pqr

Upvotes: 1

Mohammad Arshad Alam
Mohammad Arshad Alam

Reputation: 9862

The main problem i found that is your variable declaration i.e. @code varchar(10). It fixes its size to 10. You can try belwo :

Declare @code varchar(max) = 'pc-shr,pqr,qzcx,rtde,rz-fg,kj-hl,jk_lm';    
IF LEN(@code) > 10 
BEGIN
    SET @code=SUBSTRING(@code, 0, 10) + ', etc...' 
END

Upvotes: 1

Nisha
Nisha

Reputation: 1437

Is this what you are looking for?

SET @code= 
    CASE WHEN LEN(@code) > 10 
        THEN SUBSTRING(@code, 0, 10) + ', etc...' 
        ELSE '' 
    END

Upvotes: 1

Related Questions