Help with basic query

Question

Help with basic query

harleen5102

SSC-Addicted

Points: 421
More actions
October 18, 2016 at 7:46 am

#313977

Hi,
I want to add string "@abc.com" next to the Employ_ref for each employee. What is the best way to do so?
USE tempdb
GO
CREATE TABLE [Staff](
[Surname] [nvarchar](255) NULL,
[Forename] [nvarchar](255) NULL,
[Employ_ref] [float] NULL,
[Branch] [nvarchar](255) NULL,
[Job_name] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Mark', 'Rogers', '123456', 'London', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Adam', 'Rimes', '789123', 'Manchester', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Allie', 'Norton', '456789', 'Birmingham', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Jason', 'Smith', '987654', 'Swindon', 'Sales Assistant')
SELECT *
FROM [Staff]
DROP TABLE [Staff]
Many Thanks in advance.

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

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

harleen5102 (10/18/2016)
Hi,
I want to add string "@abc.com" next to the Employ_ref for each employee. What is the best way to do so?
USE tempdb
GO
CREATE TABLE [Staff](
[Surname] [nvarchar](255) NULL,
[Forename] [nvarchar](255) NULL,
[Employ_ref] [float] NULL,
[Branch] [nvarchar](255) NULL,
[Job_name] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Mark', 'Rogers', '123456', 'London', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Adam', 'Rimes', '789123', 'Manchester', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Allie', 'Norton', '456789', 'Birmingham', 'Sales Assistant')
INSERT INTO [Staff] ([Surname] ,[Forename] ,[Employ_ref] ,[Branch] ,[Job_name]) VALUES ('Jason', 'Smith', '987654', 'Swindon', 'Sales Assistant')
SELECT *
FROM [Staff]
DROP TABLE [Staff]
Many Thanks in advance.

I don't understand what you are trying to do here. Are you wanting a new column or are you wanting to add that string to Employ_ref? And why is Employ_ref a float? It looks like an int to me...or perhaps a varchar if you don't need to do math with it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

I totally agree with Sean. The column should be a varchar as it's just a number to identify the employee. It could be an integer if it's an integer somewhere else and used as a self incrementing value (identity).

That said, it seems that the problem becomes easier if you just write all the columns.

SELECT [Surname]

,[Forename]

,CAST( [Employ_ref] AS varchar(18)) + '@abc.com'

,CONCAT( [Employ_ref] , '@abc.com')

,[Branch]

,[Job_name]

FROM [Staff];

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

harleen5102 SSC-Addicted Points: 421 More actions · Answer 3

Thanks Sean and Luis. I imported the records from an excel file and sql server automatically defined the data type as float. As you guys suggested, I have now updated it to int as it will be a unique number.

Thanks Luis that is exactly what i needed.