Convert column to string in SQL Select

Question

I am looking to convert a column to string where the column is a select statment and then concat with another column. This is where my confusion occurs when using CONVERT or CAST.

Example:

 SELECT employeeID
    ,name
    ,location
    ,(SELECT COUNT(DISTINCT loginsFailed)
     FROM users
     WHERE (users.employeedID = userDetails.employeeID)
        AND (users.startdate = 01-01-2013) as LoginCountFailed
  ,(SELECT COUNT(DISTINCT logins)
   FROM users
   WHERE (users.employeedID = userDetails.employeeID)
       AND (users.startdate = 01-01-2013) as LoginCount
 FROM userDetails

Now, this query works perfect in that is provides the correct number of logins and failed as integers. However, I want to use these integer as a string so i can one column. There is a reason why this needs to be one column as string.

I want to have only 4 columns, not 5. The login column I want to have is loginCountFailed/LoginCount. For example: 3/12. I need it as a string because you cannot divide by a 0 and there are times where the denominator is 0.

What is the ultimate output you want? A string such as '3/12'? Or the actual result of that division, i.e. 4? Do you only want a string to avoid a divide by zero error? — Jeff Rosenberg
– Jeff Rosenberg, Commented Mar 8, 2013 at 16:57

Serkan Arıkuşu · Accepted Answer · 2013-03-08 17:41:11Z

12

For concatanating numbers in MSSQL-2005 you should use CAST

CAST(loginsFailed AS VARCHAR(10)) + '/' + CAST(LoginCount AS VARCHAR(10))

loginsFailed and loginCount above is actually your select count distinct fragments

I hope that this works

CAST ((SELECT COUNT(DISTINCT loginsFailed) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))
+ '/' +
CAST ((SELECT COUNT(DISTINCT logins) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))

edited Mar 8, 2013 at 17:41

answered Mar 8, 2013 at 17:00

Serkan Arıkuşu

5,6295 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1188241 Over a year ago

Yes, I know to use cast. However, I cannot get the syntax correct when using the sub selects.

user1188241 Over a year ago

This works. I can see where I was making the mistake. I was casting the sub select statment wrong with the alias being in there. This was the confusing part for me.

Woot4Moo · Accepted Answer · 2013-03-08 16:56:35Z

2

DECLARE @i int
SET @i=98235

--Method 1 : Use CAST function
SELECT CAST(@i as varchar(10))

--Method 2 : Use CONVERT function
SELECT CONVERT(varchar(10),@i)

--Method 3 : Use STR function
SELECT LTRIM(STR(@i,10))

Source

answered Mar 8, 2013 at 16:56

Woot4Moo

24.4k19 gold badges103 silver badges156 bronze badges

1 Comment

user1188241 Over a year ago

I am familar with CONVERT and CAST when selecting a column, however I do not know how to CONVERT or CAST a sub Select statement and then concat the two.

Praveen Nambiar · Accepted Answer · 2013-03-08 16:58:42Z

2

You can do the following by using CAST or CONVERT:

CONVERT(VARCHAR(20), YourIntColumn)

OR

CAST(YourIntColumn AS VARCHAR(20))

answered Mar 8, 2013 at 16:58

Praveen Nambiar

4,9022 gold badges24 silver badges32 bronze badges

Comments

Kyle Hale · Accepted Answer · 2013-03-08 17:00:10Z

2

What you want to do is have a case statement to handle divide by zero logic, not switch to a string for numeric data.

SELECT employeeID
    ,name
    ,location
    ,(SELECT CASE WHEN COUNT(DISTINCT logins) = 0 then 0 ELSE
COUNT(DISTINCT loginsFailed) / COUNT(DISTINCT logins)
END
     FROM users
     WHERE (users.employeedID = userDetails.employeeID)
        AND (users.startdate = 01-01-2013) ) as LoginFailRatio
 FROM userDetails

answered Mar 8, 2013 at 17:00

Kyle Hale

8,1301 gold badge40 silver badges59 bronze badges

2 Comments

user1188241 Over a year ago

This looks like it will work if login and loginsfailed where database columns. How would this work using my example were these are aliased column names built from sub selects?

Kyle Hale Over a year ago

These values are still built using the subselect. It just combines the COUNTs() into a single subselect and then does the division conditionally on having some logins. Did you run it?

Ajo Koshy · Accepted Answer · 2013-03-08 17:06:11Z

0

I think this will do the trick

Select column1 + '/' + column2 from table1

answered Mar 8, 2013 at 17:06

Ajo Koshy

1,2252 gold badges21 silver badges33 bronze badges

Comments

PM 77-1 · Accepted Answer · 2013-03-08 17:49:44Z

0

I think you can just create an outer query here:

SELECT u.employeeID, u.name, u.location,  
       CONVERT(varchar(10), u.LoginCountFailed) + '/' + CONVERT(varchar(10), u.LoginCount) "Ratio" 
    FROM
        (
         SELECT employeeID
            ,name
            ,location
            ,(SELECT COUNT(DISTINCT loginsFailed)
             FROM users
             WHERE (users.employeedID = userDetails.employeeID)
                AND (users.startdate = 01-01-2013) as LoginCountFailed
          ,(SELECT COUNT(DISTINCT logins)
           FROM users
           WHERE (users.employeedID = userDetails.employeeID)
               AND (users.startdate = 01-01-2013) as LoginCount
         FROM userDetails
        ) u

edited Mar 8, 2013 at 17:49

answered Mar 8, 2013 at 17:05

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

1 Comment

PM 77-1 Over a year ago

@user1188241 - Sorry about that. Could you try now (I changed it a bit) and post the exact error (if it still has problems)?

Collectives™ on Stack Overflow

Convert column to string in SQL Select

6 Answers 6

2 Comments

1 Comment

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related