CODEWITHSUNDEEP
sql-serverstring
gwydion93
gwydion93

Reputation: 1923

Remove the last value after a comma in nvarchar SQL

I have a column (Col1) with nvarchar(50) values like this: '17031021__,0,0.1,1'. I want to use this value to update another column(Col2), but remove the last number after the last comma (ex: '17031021__,0,0.1'). I think I need something like this:

CREATE TABLE table1 (
Col1 nvarchar(50),
Col2 nvarchar(50)
);
UPDATE table1
SET Col1 =  '17031021__,0,0.1,1'
Select reverse(stuff(reverse(Col1), 1, 1, '')) As Col2

This is not quite right. What is the easiest way to achieve this?

Upvotes: 0

Views: 293

Answers (2)

gwydion93
gwydion93

Reputation: 1923

OK, the aforementioned solutions seemed like overkill, so I was able to find an easier solution that worked. Here is what I used generically:

 UPDATE [dbo].[table1]
 SET [Col2] = left (Col1, len(Col1) - charindex(',', reverse(Col1) + ','))

Very similar to the second solution above but with the ',' added at the end. This produced the desired result.

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

Something along the following will give you a head-start.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(100));
INSERT INTO @tbl (tokens) VALUES
('17031021__,0,0.1,1');
-- DDL and sample data population, end

SELECT *
    , LEFT(tokens, pos) AS result
FROM @tbl 
    CROSS APPLY (SELECT LEN(tokens) - CHARINDEX(',', REVERSE(tokens))) AS t(pos);

And after you feel comfortable:

UPDATE @tbl
SET tokens = LEFT(tokens, LEN(tokens) - CHARINDEX(',', REVERSE(tokens)));

-- test
SELECT * FROM @tbl;

Upvotes: 1

Related Questions