September 19, 2013 at 4:15 pm
I have a name field as Dow, Jones (NewYork) and i can get the fist name with the query below as Sean suggested yesterday as
select SUBSTRING(_Name, patindex('%[,]%',_Name) + 1, len(_Name)) as OwnerFirstName from CC
with the result as Jones (NewYork)
and the last name as
select substring(_Name, 0, charindex(',',_Name)) as OwnerFirstName from CC
with the result as "Dow"
but i want the first name only as Jones i.e. ignoring the (newYork) string
any help is appreciated
thanks
September 19, 2013 at 4:34 pm
I think that, if you took the time to learn what the code you already have is doing, you would have your answer.
PATINDEX tells you the location or index of the requested pattern in the supplied string.
CHARINDEX tells you the location or index of one string in another string
SUBSTRING gives you the portion of a string starting at the supplied position and continuing for the requested length.
See if you can attempt something from there, then come back if you are still stuck.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 19, 2013 at 8:10 pm
I agree with MM that simply knowing PATINDEX, CHARINDEX and SUBSTRING and playing around a bit, you should be able to construct a working solution.
Another alternative if you are inclined to do a little reading is to look at the 4th article in my signature links and use the PatternSplitCM FUNCTION that is in there, using a pattern like [a-zA-Z]. This would put each component of the name on a separate row and you could then combine them using either FOR XML PATH[/url] without the comma delimiters or by constructing a cross tab query like in either of these 2 articles.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Armed with the information learned in those 4 articles, the next time you post a question you may no longer feel obliged to post in the SQL Server Newbies forum.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 20, 2013 at 9:56 am
I did construct a query based of a query in stack overflow as below, but still donot understand how this works. and still cant separate the first and the last name
data
Dow, Jones (NewYork)
for first name and last name only
SELECT SUBSTRING(_Name,LEN(LEFT(_Name,CHARINDEX ('(', _NAme))), LEN(_NAme) - LEN(LEFT(_Name, CHARINDEX (')', _owner))))
from CC;
What could be the solution but gives me an error as:Invalid length parameter passed to the SUBSTRING function.
Select substring( LEFT(_NAme,charindex('(',_Name)-1),charindex(',',_Name)+1,len(LEFT(_Name,charindex('(',_Name)-1)))
from CC;
September 20, 2013 at 11:02 am
The problem is that you can't have negative lengths or positions and you will get those, so you need to protect against them.
Try this:
select
-- uncomment the following line to see what is happening with the data
-- _Name, comma, paren,
SUBSTRING(_Name, comma + 1, paren-comma-1) as OwnerFirstName
, SUBSTRING(_Name, 1, comma - 1) as OwnerLastName
from CC
cross apply (
SELECT CHARINDEX( ',', _Name + ',' ) as comma
) a(comma)
cross apply (
SELECT ISNULL(NULLIF(CHARINDEX( '(', _Name + '(', comma+1),0),comma+1) as paren
) b(paren)
Notice in the CHARINDEX functions, I have appended the character I am looking for to the end of the string I am looking in. This is to ensure we always get a match, never get a return value of zero (except in the 2nd CHARINDEX, where I included a starting offset of "comma+1", so it is possible to return a zero - see below).
Also notice in the second CHARINDEX - to find the bracket - I have put some protection in that says if the CHARINDEX returns zero, replace the zero with "comma + 1" to ensure the length calculation in the SELECT for the OwnerFirstName will never be negative and cause an error.
Any further questions, feel free to ask - it is important to understand the code if you intend to use it.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply