Geek Answers Handbook

SQL Server 2008 R2: refresh a table using a template

I have the following table with several entries:

Table: Test_One

 create table Test_One ( Cola varchar(50) );

Insert Records:

 Insert into Test_One values('1_123456'); Insert into Test_One values('123456898_121'); Insert into Test_One values('12345633_789'); Insert into Test_One values('986_12345622'); Insert into Test_One values('3457_123456221'); Insert into Test_One values('2_123456456'); Insert into Test_One values('1234567878_5674'); Insert into Test_One values('23_1234560976'); Insert into Test_One values('6_12345634234'); ... ... Millions

Now I want to update the Cola column. I want to remove part of the line from the beginning as well as from the end. The starting line ending with _ as 1_,2_,23_,6_,3457_,986_ wants to delete, and the ending line starting with _ as _121,_789,_5674 wants to delete.

The result should be:

 Cola ----------- 123456 123456898 12345633 12345622 123456221 123456456 1234567878 1234560976 12345634234 ... ...
+4
source share
2 answers

Using CHARINDEX and SUBSTRING :

 SELECT *, CASE WHEN LEN(SUBSTRING(Cola, CHARINDEX('_', Cola) + 1, LEN(Cola) - CHARINDEX('_', Cola))) >= LEN(SUBSTRING(Cola, 0, CHARINDEX('_', Cola))) THEN SUBSTRING(Cola, CHARINDEX('_', Cola) + 1, LEN(Cola) - CHARINDEX('_', Cola)) ELSE SUBSTRING(Cola, 0, CHARINDEX('_', Cola)) END AS UpdatedCola FROM Test_One
+1
source

Using CHARINDEX , left and right

 select case when char_ind > cola_len/2 then left(cola, char_ind - 1) when char_ind < cola_len/2 then right(cola, cola_len - char_ind) else --char_ind = cola_len/2; take cola cola end as new_cola from ( select cola, CHARINDEX('_', cola) char_ind, len(cola) cola_len from test_one ) as x
+1
source

More articles:


All Articles