1

I have upload an Excel file now I want to read data from that sheet with a select query, and I want to concatenate 4 columns with separator _ as Type_name

The query I used is 

select 
   *, '1' as fileid, 'Size' + '_' + 'Material' +'_' & 'Finishing' + '_' + 'Sides' as Type_Name  
from [sheet1$] 
where [Current Code] is not null or [Current Code] <> ''

All data is correctly returned by query except Type_Name

This query return Type_Name as Size_Material_Finishing_Sides

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1817367
  • 43
  • 1
  • 1
  • 5
  • have a look into the `CONCAT()` function – fancyPants Dec 12 '12 at 08:58
  • You're **explicitly** telling your query to concatenate together these string literal values: `Size`, `_`, `Material`, `_`, `Finishing`, `_`, and `Sides` - so what did **you** expect to get from this ?? – marc_s Dec 12 '12 at 09:15
  • @marc_s - I think the point is the OP *wants* to get this result, but is not getting it. – dan1111 Dec 12 '12 at 09:29
  • @dan1111: not quite clear - I understood he's *getting* `Size_Material_Finishing_Sides` but really wants something else .... – marc_s Dec 12 '12 at 09:32
  • @marc_s, I agree that it is not clear. – dan1111 Dec 12 '12 at 09:39
  • Size ,Material,Finishing,Sides are columns in Excel sheet i want concatinate value of these fields with seprator '_' – user1817367 Dec 12 '12 at 10:07
  • @user1817367: Quotation marks (`'`) are used to delimit string literals. If `Size`, `Material` et al. are names, then do not delimit them with `'`. I.e. it should probably look something like this: `Size + '_' + Material + '_' + ...`. And, as @dan111 has already pointed in their answer, you are using both `+` and `&` as concatenation operators. Shouldn't it be either one or the other? – Andriy M Dec 12 '12 at 12:19

2 Answers2

0

This depends on the type of SQL you are using.

I assume you are querying from within Excel or from MS Access. In this case you should use String1 & String2 & ....

However, here are other options:

SQL Server uses String1 + String2 + ....

Oracle, DB2, and some versions of MySQL use String1 || String2 || ....

The method supported across the most versions of MySQL is CONCAT(String1, String2, ...).

CONCAT() exists in most versions of SQL, but beware: you can't always use it to combine an arbitrary number of strings. In Oracle and DB2, for example, this function can only combine two strings.

The example you posted includes a mixture of + and &, which definitely won't work.

dan1111
  • 6,576
  • 2
  • 18
  • 29
-1

You are using & and + to concatenate? Why are you concatenating in the first place? Why not just use:

'Size_Material_Finishing_Sides' as Type_Name

If this is not the result you expected what result do you expect?

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
  • Presumably, this is just a simplified example, and the OP actually wants to concatenate some columns in the result. – dan1111 Dec 12 '12 at 09:31