splitting column into two
-
Hello
I am working to split one colume named "name" that includes the LastName/Firstname seprated with "/" i tried to create the following query to get the Lastname and firstname separated
SELECT LEFT(Name, CHARINDEX('/', Name)) AS Lastname, SUBSTRING(Name, CHARINDEX('/', Name) + 1, LEN(Name) - CHARINDEX('/', Name)) AS Firstname
FROM dbo.[Sales]
the problem is i couldn't get rid of the slash here .. it comes in the output as part of the lastname any idea how to come over this problem
thanksRashed
-
maybe...
DECLARE @name VARCHAR(50)= 'baggins/bilbo';
SELECT LEFT(@Name, CHARINDEX('/', @Name)-1) AS Lastname,
SUBSTRING(@Name, CHARINDEX('/', @Name)+1, LEN(@Name)-CHARINDEX('/', @Name)) AS Firstname;________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Thank you , whenever i used the -1 , i get error message
- alhakimi - Sunday, September 3, 2017 1:01 PMThank you , whenever i used the -1 , i get error message
do you have names that DO NOT include a '/'
for exampleDECLARE @name VARCHAR(50)= 'bagginsbilbo';
SELECT LEFT(@Name, CHARINDEX('/', @Name)-1) AS Lastname,
SUBSTRING(@Name, CHARINDEX('/', @Name)+1, LEN(@Name)-CHARINDEX('/', @Name)) AS Firstname;________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - alhakimi - Sunday, September 3, 2017 11:59 AM
Just replace the slash with an empty string
😎 -
hmm , yes i found out that some names are without / , what should be the best practice here ?
- alhakimi - Sunday, September 3, 2017 1:26 PM
well, as no one on here knows your data as well as you do, then I would post as per this article
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and provide all possible permutations (and results) you expect from your analysis of your data[/code] and provide all possible permutations (and results) you expect from your analysis of your data________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - alhakimi - Sunday, September 3, 2017 1:26 PM
Clean your data. Always know what your data contains before writing code to do something with it, especially when it comes to splitting the data to component parts. Then, identify what you need to do.
You haven't identified what the names without slashes look like, making it impossible for us to give you any other suggestion other than the one above. For example, do the names than have no slashes still contain both a LastName and a FirstName? If so, what character is used as the delimiter? If the name with no slashes only has one part, should it be treated as a LastName, a FirstName, or an error?
Once you've figured out the different conditions that your data could be in, then you need to post the rules you've established AND the data in a readily consumable fashion to make it easy for the people who are trying to help you. Please see the first link under "Helpful Links" in my signature line below for how to do that correctly.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You can use something like the following... If the "/" delimiter is present, then it will split the value into FirstName & LastName. If the delimiter is not present then it will put the entire string into FirstName and NULL the LastName.
SELECT
FirstName = LEFT(mt.Name, ISNULL(s.Split - 1, 8000)),
LastName = SUBSTRING(mt.Name, s.Split + 1, s.Split + 8000)
FROM
dbo.MyTable mt
CROSS APPLY ( VALUES (NULLIF(CHARINDEX('/', mt.Name, 1), 0)) ) s (Split);If you want something trickier than that, you'll need to post some representative data samples and let us know what the output should should look like based on the samples.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply