March 4, 2015 at 6:23 am
Hi all.
I am trying to build a view of a table with a new column that will filter some characters in an existing table. The existing table contains a field called useremail with a value "user1@email.com".
In the view, i want the view to have a column that will use the useremail data but without @email.com, showing only the "user1" in the new column. Please HELP
Thanks!
March 4, 2015 at 8:51 am
DECLARE @column varchar(50) = 'user1@email.com';
SELECT LEFT(@column, CHARINDEX('@', @column, 1) -1);
March 4, 2015 at 12:22 pm
Thank you Laurie..but how do i modify this so i can pull all the other records in that column? Thanks in advance:-)
March 4, 2015 at 2:18 pm
eedgar 45478 (3/4/2015)
Thank you Laurie..but how do i modify this so i can pull all the other records in that column? Thanks in advance:-)
What do you mean by "pull all the other records in that column"??? The fine example that Laurie posted just used a variable. Change the variable to be your column name and include any other columns you want in your query. A word of caution...if a row doesn't have an "@" in the value the charindex will return 0 which will raise an error that an invalid value was passed the Left function.
_______________________________________________________________
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/
March 4, 2015 at 2:52 pm
CREATE VIEW ...
AS
SELECT ..., LEFT(useremail, CHARINDEX('@', useremail + '@') - 1) AS , ...
FROM existing_table
--WHERE ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 5, 2015 at 1:21 am
USE [tempdb]
GO
--== TEST DATA ==--
IF OBJECT_ID('dbo.UserDetails') IS NOT NULL DROP TABLE dbo.UserDetails
CREATE TABLE dbo.UserDetails
(
StaffNumber Varchar(30),
Name Varchar(30),
UserEmail Varchar(50) NULL
)
INSERT INTO dbo.UserDetails
(StaffNumber, Name, UserEmail)
VALUES
('A111116', 'Fred Smith', 'user1@email.com'),
('B22371', 'Jan Richards', NULL),
('X06742198', 'Anne Spencer', 'annespencer@email.com'),
('B43521', 'John James', 'jj-invalid')
SELECT * FROM dbo.UserDetails
--== CREATE VIEW ==--
-- It's best to run this code command-by-command from here, to see what's happening:
IF OBJECT_ID('dbo.View1') IS NOT NULL DROP VIEW dbo.View1
GO
-- This view doesn't handle invalid email addresses (NULL is OK):
CREATE VIEW dbo.View1 AS
SELECT *,
[NewColumn]=LEFT(UserEmail, CHARINDEX('@', UserEmail, 1) -1)
FROM dbo.UserDetails
GO
-- This fails:
SELECT * FROM dbo.View1
GO
-- Handle invalid data:
ALTER VIEW dbo.View1 AS
SELECT *,
[NewColumn]=CASE WHEN CHARINDEX('@', UserEmail, 1)=0 THEN UserEmail ELSE LEFT(UserEmail, CHARINDEX('@', UserEmail, 1) -1) END
FROM dbo.UserDetails
GO
-- And it works...
SELECT * FROM dbo.View1
-- ... but you may want to handle missing/invalid data differently.
March 5, 2015 at 5:58 am
THANK YOU ALL for all the help! both suggestions work perefectly. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply