How can I Prepend 2 CHAR in front of an INT?

Question

Post reply

How can I Prepend 2 CHAR in front of an INT?

chef423

SSCertifiable

Points: 5279
More actions
October 17, 2014 at 4:27 pm

#297667

SELECTc.cust_fullname AS Name,
c.cust_membership_id AS Account,
t.c_amount AS Amount,
--CONVERT(VARCHAR(12), t.i_ticket_id, + 'CH') AS Doc_Num,
t.s_credit_tran_type AS Detail_Account,
CONVERT(varchar(10), t.dt_when, 110) AS SaleDate
-Output now-
DocNum
56522
-Output Needing-
DocNum
CH56522
Thanks to anyone who can assist...
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

SELECTc.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

'CH' + CAST(t.i_ticket_id AS varchar(12)) AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

CONVERT(varchar(10), t.dt_when, 110) AS SaleDate

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

chef423 SSCertifiable Points: 5279 More actions · Answer 2

wow, I didnt even get time to edit my question, you sir, are a beast 🙂

Thanks.

chef423 SSCertifiable Points: 5279 More actions · Answer 3

One last question:

SELECTc.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

'CH' + CAST(t.i_ticket_id AS varchar(12)) AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

CONVERT(varchar(10), t.dt_when, 110) AS SaleDate

FROM dbo.Transactions AS t INNER JOIN

dbo.Customers AS c ON t.s_ref_num = c.cust_id

WHERE (t.s_credit_tran_type = 'House Account') AND (t.b_cancel = 0) AND (t.dt_when >= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7 - 7, '17530101')) AND

(t.dt_when <= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7, '17530101'))

GROUP BY c.cust_fullname, c.cust_membership_id, t.c_amount, t.i_ticket_id, t.s_credit_tran_type, t.dt_when

The SQL code above outputs this query:

ELLIES MUTT HUTTAccounts Receivable91.57 CH56522 House Account 10-06-2014

PACE SUPPLY Accounts Receivable60.33 CH56521 House Account 10-06-2014

But I need it to output this, but there is no table in SQL for me to pull the word 'Food'

So I need FOOD, in its own column for every time there is a row output in the SQL Query...

Like this:

ELLIES MUTT HUTTAccounts Receivable91.57 CH56522 House Account 10-06-2014 FOOD

PACE SUPPLY Accounts Receivable60.33 CH56521 House Account 10-06-2014 FOOD

Google no help as I dont really know how to ask the question.

Thanks again to anyone who can help...

Chris

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You just add a string literal as the column.

SELECTc.cust_fullname AS Name,

c.cust_membership_id AS Account,

t.c_amount AS Amount,

'CH' + CAST(t.i_ticket_id AS varchar(12)) AS Doc_Num,

t.s_credit_tran_type AS Detail_Account,

CONVERT(varchar(10), t.dt_when, 110) AS SaleDate,

'FOOD' AS Something

FROM dbo.Transactions AS t INNER JOIN

dbo.Customers AS c ON t.s_ref_num = c.cust_id

WHERE (t.s_credit_tran_type = 'House Account') AND (t.b_cancel = 0) AND (t.dt_when >= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7 - 7, '17530101')) AND

(t.dt_when <= DATEADD(dd, DATEDIFF(dd, '17530101', GETDATE()) / 7 * 7, '17530101'))

GROUP BY c.cust_fullname, c.cust_membership_id, t.c_amount, t.i_ticket_id, t.s_credit_tran_type, t.dt_when

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2