Replace

• arun1989klncit

  SSC Rookie

  Points: 26

  More actions

  March 28, 2012 at 10:25 pm

  #147204

  Hi All,

  I am new to SQL SERVER.,

  i have table with below data in my database.

  table name : tbl_orders

  Select Order_Number from tbl_orders :

  Order_number

  --------------------------------------------------------------------

  460-91383

  206-62191

  460-91442

  460-91351

  18069330282

  18069360180

  18069400282

  18069430180

  18069440180

  18069470282

  in this table, i have two different types of order number ie.,

  a)order number with special character '-' (eg . 206-62191)

  b)order number without special character (eg .18069470282 )

  i have to edit the order number without special character., ie., for these order number the last four digits should be removed

  for example

  actual data --- required output

  18069470282 --- 1806947

  18069440180 --- 1806944

  So overall datas should look like as below :

  Required output

  Order_number

  --------------------------------------------------------------------

  460-91383

  206-62191

  460-91442

  460-91351

  1806933

  1806936

  1806940

  1806943

  1806944

  1806947

  Hope my question is clear, so could someone please help me to get this data.

  Thanks in Advance,

  Arun Manas

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 28, 2012 at 11:19 pm

  #1466167

  How about the following:

  select

  case when CHARINDEX('-',OrderNum) > 0

  then OrderNum

  else SUBSTRING(OrderNum, 1,LEN(OrderNum) - 4)

  end

  from (

  select '460-91383' as OrderNum union all

  select '206-62191' union all

  select '460-91442' union all

  select '460-91351' union all

  select '18069330282' union all

  select '18069360180' union all

  select '18069400282' union all

  select '18069430180' union all

  select '18069440180' union all

  select '18069470282') testdata

  ;

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 28, 2012 at 11:25 pm

  #1466171

  Should be careful with the trailing spaces.

  declare @var varchar(100) = '1234567890 '

  select len(@var)

  select datalength(@var)

  select SUBSTRING(@var, 1, len(@var)-4) , SUBSTRING(@var, 1, datalength(@var)-4)

  Len datalength

  -------------------- --------------------

  123456 1234567890

• arun1989klncit

  SSC Rookie

  Points: 26

  More actions

  March 28, 2012 at 11:26 pm

  #1466172

  Thanks a lot., it working.,