January 7, 2016 at 9:55 am
Hi,
I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in the document. Rest of the query is working fine as per requirement .
Can anyone please help me in writing the point 3 of this word document. I really appreciate for the help in advance.
January 7, 2016 at 9:58 am
Zohaib Anwar (1/7/2016)
Hi,I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in the document. Rest of the query is working fine as per requirement .
Can anyone please help me in writing the point 3 of this word document. I really appreciate for the help in advance.
Can you post the actual contents of that document? I am not going to download a rar file that contains a word document to my system from somebody I don't know. If you can post the actual details in your question I will be willing to help you.
_______________________________________________________________
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/
January 7, 2016 at 9:59 am
Here it is..
Get_NameDetails
1.Set Set lcLast_Name = get (character position of lcSeperator in 1301_Prestage_PID.cname) - 1 from 1301_Prestage_PID.cname[48 Charactrers]
2.Set lcFirst_Name = get (character position of lcSeperator in 1301_Prestage_PV1.cname) + 1 from 1301_Prestage_PV1.cname and remove ‘.’ as blank.
3.When Character position of lcSeparator in lcFirst_Name is greater than 0 AND character posiition of lcSeparator in lc_First_Name equal to length of lcFirst_name -1 then
a.Set lcMinitial = last 1 characater from lcFirst_Name and lcFirst_name = first length of lcFirst_Name -2 from lcFirst_name.
4.Otherwise
a.Set lcMinitial = blank space 1 and lcFirst_name = remove lcSeparator as a blank in lcFirst_name
5.When first character of lcFirst_name not between ‘A’ to ‘Z’ then Set lcFirst_Name = first 2 character from lcFirst_Name
6.Set lcGuar_First_Name = first character position of lcSeparator in 1301_Prestage_GT1.cname – 1 [ALLTRIM(LEFT(1301_Prestage_GT1.cname,AT(lcSeparator,1301_Prestage_GT1.cname)-1))]
7.Set lcGuar_First_Name = upto character position lcSeparator in 1301_Prestage_GT1.cname +1 from 1301_Prestage_GT1 and replace ‘.’ With blank.
8.When Character position of lcSeparator in lcGuar_First_Name is greater than 0 AND Character position of lcSeparator in lcGuar_First_Name = length of lcGuar_First_name -1 then
a.Set lcGuar_Minitial = last character from lcGuar_First_name and lcGuar_First_Name = first length of lcGuar_First_name -2 from lcGuar_First_Name.
9.Otherwise
a.Set lcGuar_Minitial = space 1 and replace lcSeparator with blank in lcGuar_First_name and take this value
10.When first character of lcGuar_First_name not between ‘A’ to ‘Z’ then Set lcGuar_First_Name = first 2 character from lcGuar_First_Name
Following is the query I have prepared from above code
Declare
@lcFirst_Name VARCHAR(20) ,
@lcLast_Name VARCHAR(100) ,
@lcGuar_First_Name VARCHAR(20),
@lcGuar_Last_Name VARCHAR(20),
@lcMinitial VARCHAR(20) = '',
@lcGuar_Minitial VARCHAR(20) = '',
@PIDcName VARCHAR(100) = 'Sumair^Haider^R',
@GITcName VARCHAR(100) = 'Ali^Haider.',
@Pidx INT = 0,
@Gidx INT = 0
SET @Pidx = CHARINDEX('^',@PIDcName)
SET @lcFirst_Name = RTRIM(LTRIM(SUBSTRING(@PIDcName,1,@Pidx-1)))
SET @lcLast_Name = REPLACE(RTRIM(LTRIM(SUBSTRING(@PIDcName,@Pidx+1,LEN(@PIDcName)))),'.','')
IF SUBSTRING(@lcFirst_Name,1,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
SET @lcFirst_Name = SUBSTRING(@lcFirst_Name,1,2)
SET @Gidx = CHARINDEX('^',@GITcName)
SET @lcGuar_First_Name = RTRIM(LTRIM(SUBSTRING(@GITcName,1,@Gidx-1)))
SET @lcGuar_Last_Name = REPLACE(RTRIM(LTRIM(SUBSTRING(@GITcName,@Gidx+1,LEN(@GITcName)))),'.','')
IF SUBSTRING(@lcGuar_First_Name,1,1) NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
SET @lcGuar_First_Name = SUBSTRING(@lcGuar_First_Name,1,2)
SELECT @lcFirst_Name as lcFirst_Name, @lcLast_Name as lcFirst_Name
SELECT @lcGuar_First_Name as lcGuar_First_Name, @lcGuar_Last_Name as lcGuar_Last_Name
January 7, 2016 at 10:21 am
Will this work:
Declare
@lcFirst_Name VARCHAR(20) ,
@lcLast_Name VARCHAR(100) ,
@lcGuar_First_Name VARCHAR(20),
@lcGuar_Last_Name VARCHAR(20),
@lcMinitial VARCHAR(20) = '',
@lcGuar_Minitial VARCHAR(20) = '',
@PIDcName VARCHAR(100) = 'Sumair^Haider^R',
@GITcName VARCHAR(100) = 'Ali^Haider.',
@Pidx INT = 0,
@Gidx INT = 0
SELECT [1] as FirstName,
[2] as LastName,
[3] as MiddleInitial
FROM (SELECT * FROM dbo.DelimitedSplit8k(@PIDcName, '^')) SRC
PIVOT(MIN(ItemValue) FOR ItemNumber IN ([1], [2], [3])) PVT
SELECT [1] as FirstName,
[2] as LastName,
[3] as MiddleInitial
FROM (SELECT * FROM dbo.DelimitedSplit8k(@GITcName, '^')) SRC
PIVOT(MIN(ItemValue) FOR ItemNumber IN ([1], [2], [3])) PVT
The code can be downloaded here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 8, 2016 at 3:26 am
Sean Lange (1/7/2016)
Zohaib Anwar (1/7/2016)
Hi,I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in the document. Rest of the query is working fine as per requirement .
Can anyone please help me in writing the point 3 of this word document. I really appreciate for the help in advance.
Can you post the actual contents of that document? I am not going to download a rar file that contains a word document to my system from somebody I don't know. If you can post the actual details in your question I will be willing to help you.
Please help me I have mentioned the the contents of the document and SQL query which I have written so Far.
January 8, 2016 at 5:16 am
Zohaib Anwar (1/8/2016)
Sean Lange (1/7/2016)
Zohaib Anwar (1/7/2016)
Hi,I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in the document. Rest of the query is working fine as per requirement .
Can anyone please help me in writing the point 3 of this word document. I really appreciate for the help in advance.
Can you post the actual contents of that document? I am not going to download a rar file that contains a word document to my system from somebody I don't know. If you can post the actual details in your question I will be willing to help you.
Please help me I have mentioned the the contents of the document and SQL query which I have written so Far.
Did you look at the code posted by Michael? Did it work?
I admit that I have not looked at it. I must also add that I do not like doing this logic in SQL Server, it's not a tool designed for string manipulation. C# and other .Net code has a lot more functionality for this.
But if you have to do it in T-SQL, here is a possible answer to your point #3:
3. When Character position of lcSeparator in lcFirst_Name is greater than 0 AND character posiition of lcSeparator in lc_First_Name equal to length of lcFirst_name -1 then
a. Set lcMinitial = last 1 characater from lcFirst_Name and lcFirst_name = first length of lcFirst_Name -2 from lcFirst_name.
IF LEN(@lc_First_Name) > 1 AND CHARINDEX(@lcSeperator, @lcFirst_Name) = LEN(@lc_First_Name) - 1
BEGIN;
SET @lcMinitial = RIGHT(@lc_First_Name, 1);
SET @lcFirst_Name = LEFT(@lcFirst_Name, LEN(@lcFirst_Name) - 2);
END;
(Note that the above is untested)
(Also note that the entire description of what you need to do should actually be discarded. When working in SQL, always reject functional descriptions that are already at the step-by-step level. Instead, ask for a functional description of what needs to be done, then write a query from that)
January 8, 2016 at 7:36 am
Hugo Kornelis (1/8/2016)
Zohaib Anwar (1/8/2016)
Sean Lange (1/7/2016)
Zohaib Anwar (1/7/2016)
Hi,I am trying to build attached query that is mentioned in the word document. I am struck on one point that is point number 3 in the document. Rest of the query is working fine as per requirement .
Can anyone please help me in writing the point 3 of this word document. I really appreciate for the help in advance.
Can you post the actual contents of that document? I am not going to download a rar file that contains a word document to my system from somebody I don't know. If you can post the actual details in your question I will be willing to help you.
Please help me I have mentioned the the contents of the document and SQL query which I have written so Far.
Did you look at the code posted by Michael? Did it work?
I admit that I have not looked at it. I must also add that I do not like doing this logic in SQL Server, it's not a tool designed for string manipulation. C# and other .Net code has a lot more functionality for this.
But if you have to do it in T-SQL, here is a possible answer to your point #3:
3. When Character position of lcSeparator in lcFirst_Name is greater than 0 AND character posiition of lcSeparator in lc_First_Name equal to length of lcFirst_name -1 then
a. Set lcMinitial = last 1 characater from lcFirst_Name and lcFirst_name = first length of lcFirst_Name -2 from lcFirst_name.
IF LEN(@lc_First_Name) > 1 AND CHARINDEX(@lcSeperator, @lcFirst_Name) = LEN(@lc_First_Name) - 1BEGIN;
SET @lcMinitial = RIGHT(@lc_First_Name, 1);
SET @lcFirst_Name = LEFT(@lcFirst_Name, LEN(@lcFirst_Name) - 2);
END;
(Note that the above is untested)
(Also note that the entire description of what you need to do should actually be discarded. When working in SQL, always reject functional descriptions that are already at the step-by-step level. Instead, ask for a functional description of what needs to be done, then write a query from that)
Heh... Ok... I'm thinking that when we someday meet, I'll have a fish for ya. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply