Combine Varchar Column with Int Column

Combine varchar column with int column

String concatenation is different between databases, so it helps to know which database because you need to know:

1. The concatenation method/operator
2. If the database handles implicit data type conversion

SQL Server doesn't do implicit conversion of numeric into string values:

SELECT CAST(fooid AS VARCHAR(10)) + ' ' + fooname

...so you need to use CAST (or CONVERT) to explicitly change the data type to a text based data type.

For Oracle & PostgreSQL, use the double pipe to concatenate strings:

SELECT fooid || ' ' || fooname

For MySQL, you can use the CONCAT function:

SELECT CONCAT(fooid, ' ', fooname)

How to combine 2 int value columns to varchar with specified length

You can pad the month format so that it is forced to display 2 numbers.

SELECT CAST(Year AS nvarchar(20)) + '-' 
+ RIGHT('00' + CAST(Month AS nvarchar(20)),2) AS newcolumn from table_name

Sample with Getdate():

SELECT CAST(YEAR(GETDATE()) as nvarchar(20)) + '-' +
       RIGHT('00' + CAST(MONTH(GETDATE()) as NVARCHAR(2)),2)

Concatenate a Varchar and int

SELECT ('VarValue' + CAST(32 AS VARCHAR))

MS Access - Combine varchar column with int column - ASP.net VB.net

That's because you try to use T-SQL against Access which uses Access SQL. So, try this:

Dim strQuery As String = "SELECT ProductID, ProductID & ' - ' & 
                  ProductCode & ' - ' & ProdDescription AS [Prod] 
                  FROM [FG - End Product Codes] 
                  ORDER BY ProductCode;"

Concatenate and add a character to integer columns in SQL

You can't use the + operator with a numerical data type and a varchar that cannot implicitly be converted to that data type. Something like 1 + 'a' isn't going to work, as 'a' isn't an int, and can't be implicitly converted to one.

If you are mixing data types, then use CONCAT, which implicitly converts each part into a (n)varchar:

CONCAT({Numerical Expression},'a',{Other varchar Expression})

In SQL, how to concatenate string with integer data type column

You need to explicitly convert the int to varchar before concatenating with any string

select 'RS'+cast(total_amount as varchar(100)),*
from yourtable

If you are sql server 2012+ then use CONCAT which does implicit conversion

select Concat('RS',total_amount),*
from yourtable

SQL Server - Better Way To Join Between VARCHAR and INT

I think you are on the right track. You just need the right expression for like:

SELECT POID, SKU,
       (SELECT SUM(ShipQuantity) AS ShipQuantity 
        FROM tblVendorShippingInfo vsi
        WHERE substring(vsi.PONum, 2, 6) = Convert(varchar(10), pod.POID) AND
              vSKU = pod.SKU
       ) as QtyCount
FROM tblPODetail pod 
WHERE PONum like '[A-Za-z][0-9][0-9][0-9][0-9][0-9][0-9]';

If you want to index this as an integer, then use a computed column:

alter table tblVendorShippingInfo add column POID_num as try_convert(int, substring(PONum, 2, 6);

create index idx_tblVendorShippingInfo_POID on tblVendorShippingInfo(POID_num);

try_convert() assumes SQL Server 2012+. You can do something similar with earlier versions of SQL Server.

Sql concatenate integer columns to string

Try like this:

select CAST(A as varchar(10)) + CAST(B as varchar(10))

SQL DEMO

Can I LEFT JOIN between column types INT and VARCHAR?

This seems the best solution to me:

SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users 
ON cast(Students.PERSON_CODE as varchar(10)) = users.username
WHERE Students.PERSON_CODE > 0 

This way you don't need to check if username is an integer. You simply convert PERSON_CODE to a varchar(10) before comparing and problem is solved.

How to concatenate all the rows of a single int column to get a single string?

Your query is almost correct. You just do not need the part with substring. Also I suggest you to order rows while concatenating with for xml path. Do you have some ID column? I have slightly modified your query:

select result = (
    Select ''+ST1.[state]  AS [text()]
    From dbo.table_1 ST1
    For XML PATH ('')
)

---

Related Topics