Concatenate

• Tsql-561486

  SSC Eights!

  Points: 821

  More actions

  October 9, 2008 at 9:26 am

  #71930

  Hi,

  I have table T1

  Col1 Col2

  1 09

  02 01

  01 1

  I have to concantenate the columns

  Using the below query

  SELECT CAST(col1 AS varchar) + CAST( col2 AS varchar) AS ConcatenatedINTs from T1

  result set is

  ConcatenatedINTs

  109

  0201

  011

  But i require result as

  19

  21

  11

  i.e to eliminate the '0' from both columns, if it is the first digit of thwe column

  Please help

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 9, 2008 at 9:34 am

  #882979

  SELECT REPLACE(CAST(col1 as varchar(20)) + CAST(col2 as varchar(20)),'0','')

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 9, 2008 at 9:59 am

  #882997

  why not convert them to ints, then cast then as varchars together?

  CREATE TABLE T1(col1 varchar(30),col2 varchar(30) )

  insert into T1

  SELECT '1','09' UNION

  SELECT '02','01' UNION

  SELECT '01','1 '

  SELECT

  convert(varchar,convert(int,col1))

  + convert(varchar,convert(int, col2)) AS ConcatenatedINTs from T1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 9, 2008 at 10:19 am

  #883015

  Yeah... don't do what I posted, that's what I get for trying to respond real quick before I answered someone's email. That way will have you dropping all your zero's.

  Lowell's method should work great for you though.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]