I have 2 data flows tasks, salesperson and customer, where I want to concatenate name and Lastname into Name, using a Derived Column...
[name] + " " + Lastname
In the first one, Destination Preview show NULL on every Name row. In the other the names are joined as "DanWinter". Why don't the concatenate according to expression?
My users wanted to view all the "FIRST and LAST" Lastname in each of the LastName initial in the database.
I have been trying to join/connect/subquery/merge/etc the following two set of codes but without good results.
Fist, i get all the lastname initials:
SELECT DISTINCT LEFT(LastName, 1) AS LastNameInitial
FROM Persons.Persons
ORDER BY LastNameInitial
Results to:
--------------------------------------
A
B
C
D
...
Y
Z
Then, i want to get all the FIRST and LAST LastName for each LastNameInitial
I only have this partial idea (can't get the rest):
--------------------------------------
SELECT MIN(LastName) + ' - ' + MAX(LastName) AS LastNameGroup
FROM Persons.Persons
WHERE (LastName LIKE 'A' + '%')
Only results to:
--------------------------------------
Abad - Ayun
MY USERS WANTED TO HAVE THIS RESULT (In a single query):
------------------------------------------
Abad - Ayun
Babaran - Buted
Cabaccan - Curugan
Dacquil - Durwin
Eda - Evangelista
...
Yadao - Yumul
Zalun - Zunajo
Thanks in advance for your help.
God bless!
Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?
View 14 Replies View RelatedHow to retrieve data as a two names from one columns.
For example: My column has naming like this Shannon.Cooley, I want to display like this:
FirstName LastName FullName
Shannon Cooley
Shannon Cooley
How to write the query to get the above table?
I'm sure you all have seen situations where a field has a combined namein the format of "Lastname, Firstname Middlename" and I've seenexamples of parsing that type of field. But what happens when the datawithin this type of field is inconsistent? Here are some examplesApple, John A.Berry John B.CherryJohn CDonald John DHow does one parse the data when the data isn't consistent?
View 2 Replies View RelatedHello fellows,
I am trying to write a query, here is the detail:
I have a table of users. Table schema:
FULL NAME, LOGIN
example data:
Anderman John, AndermaJ
Williams Robin, WilliamR
Foot Jay, FootJ
The LOGIN field will be my resulting field after I run my query.
It has a "FULL NAME" column, and a "LOGIN" column. I want to extract the first 7 characters of LAST Name from the FULL NAME field and extract the First Name Initial(just first letter of the first name) from the FULL NAME field and then set the LOGIN field with the resulting combination. The FULL NAME column is in the order of LastName FirstName.
Here is what I have been trying to write:
Declare @spacePos as int
Select @spacePos = charindex(' ', fullname) From TIUSER
Update TIUSER
SEt LOGIN = ltrim(Substring(fullname, 1, 7)) + Substring(fullname,@spacePos+1,1),
where @spacePos <> 0 and LOGIN = ' '
Currently we only have last and first names in our database and we will keep it that way since we don't store middle names and hopefully that will not be an issue.
But it seems that my solution was not working since if the last name is less than 7 characters then it should only grab upto the SPACE and not beyond that , but it was grabbing beyond the SPACE so how do you fix that?
That is I want atmost 7 characters from LAST NAME but incase of less than 7 then upto the space found.
Any help would be highly appreciated.