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'
Is this what you are looking for?
SET @code=
CASE WHEN LEN(@code) > 10
THEN SUBSTRING(@code, 0, 10) + ', etc...'
ELSE ''
END
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
Query:
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
