Extract First Name From String

How can I extract the First Name only from the String Below? I only want to
pick up 'Veronica'
I can get the Last Name by using CHARINDEX(',',[Patient Name])-1


[Patient Name]   =     LUNA,VERONICA E

-- 
Brian Conner

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1

0
Brian
12/9/2009 2:49:15 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
980 Views

Similar Articles

[PageSpeed] 38

Brian

SELECT LEFT(@Value, CHARINDEX(',', @Value)-1),



"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message 
news:a0558fa5b6081@uwe...
> How can I extract the First Name only from the String Below? I only want 
> to
> pick up 'Veronica'
> I can get the Last Name by using CHARINDEX(',',[Patient Name])-1
>
>
> [Patient Name]   =     LUNA,VERONICA E
>
> -- 
> Brian Conner
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
> 


0
Uri
12/9/2009 2:59:15 PM
There could be many variations of a name and handling all cases can be very complex. Here is one query that will work 
with your sample data:

SELECT CASE WHEN CHARINDEX(',', [Patient Name]) > 0
             THEN SUBSTRING([Patient Name], CHARINDEX(',', [Patient Name]) + 1,
                         CASE WHEN CHARINDEX(' ', [Patient Name], CHARINDEX(',', [Patient Name]) + 1) > 0
                              THEN CHARINDEX(' ', [Patient Name], CHARINDEX(',', [Patient Name]) + 1) -
                                   (CHARINDEX(',', [Patient Name]) + 1)
                              ELSE LEN([Patient Name]) - CHARINDEX(',', [Patient Name])
                         END)
        END AS first_name
FROM Patients;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/9/2009 3:35:20 PM
Reply:

Similar Artilces:

Err.Description as error proc name trail, error handling
Hi All, I'm looking for guidance on how to retrofit into a large AddIn the 'best' or possible uses of the Err object in providing an information path to help track down run time errors in a 'production' environment. Some Background, 4 questions follow. I was new to VBA, self taught, when the coding started, (but with experience in other languages), and the vba error handling seemed too complex at the time, huge mistake on my part. The procs below are learning vehicles, which I'm just starting to play with, but they do show the essence o...

Outlook 2003 Email Select Names Type name or select from list
New Message: When I try to use the Select Names - Type name or select from list in an Email New Message, the search is not as expected. For example, I enter the Name Smith, and the selection will be another name starting with S, but not Smith. This Address was from Outlook Express and worked fine there. Thank you. Please describe more completely what you are doing. Perhaps you are using autocompletion instead of autoresolution. You didn't provide enough details for us to tell. -- Russ Valentine [MVP-Outlook] "nsnews.microsoft.com" <nfn02958@naples.net> wrote in message...

how to get remote host name
Hello! I have a problem with getting the hostname of the other part of the connection. I had used gethostbyaddr() to retreive the hostname of the other side. (When I am the server.) If the server and the client in the same subnetwork everything work just fine. But as the client come from an other subnetwork this function fails and get back a HOSTENT* structure with NULL value and with and error: this host exists but no associated name. My question is about how to obtain in this case the hostname of the client? There is a general way? Some function? Thanks in advance. Best Regards, Nagyja...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

Page Numbering & First page Different 2007
Page numbering and controlling the first page is wrong most the time. Is there an alternative by going thru fields and stopping the first page from having a number on it. Word is too capricious, forgets where it is, etc... Hi, Detailed instructions for adding page numbers in Word 2007 are given in http://office.microsoft.com/en-us/word/HP012265131033.aspx . If you are placing your page numbers in headers or footers, the section-specific settings that you configure according to those instructions override any switches that you might add to the PAGE field. If you keep this ru...

Hiding some labels on value axes; Changing series names
I've created a column chart in Excel 2002. It has 9 columns, increasing in value from left to right. The 8 columns on the left have values ranging from 2.7 to 12. The 9th column has a value of 51.8. The primary y axis shows the values for the 8 columns, while the secondary y axis shows the value for the 9th column. First question. The primary y axis scale goes from 0 to 16 with a major unit of 4. The secondary y axis scale goes from 0 to 55 with a major unit of 5. I would like to hide some of the numbers shown on each axis, so as to make it clearer which axis is the relevant one for each...

In Excel how do I force text into next line before first is full?
,I am using Office XPPro (PC) but although I used Word a lot before I retired I am only now trying to do things in Excel (and incidentally Access). I want to put the membership records of a club into an Excel spreadsheet and to show each postal address divided (as it would be on an envelope) into several lines, some of them short. Since, when I am typing in data, pressing the Return key takes me to the next cell, not the next line, at present wherever I want a line break I am having to put in enough extra spaces to push the next word over the edge of the cell so that it wraps i...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Error binding display name
Hello, I have just followed KB article 317327 on my SBS 2003 machine so as to add an email disclaimer. I got the below error when trying to register the sink: - C:\PROGRA~1\Exchsrvr>cd scripts C:\PROGRA~1\Exchsrvr\scripts>cscript smtpreg.vbs /add 1 OnArrival SMTPDisclaimer SMTPEventSink.Disclaimer "mail from=*@mydomain.com" Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. Binding Display Name Specified: SMTPDisclaimer ** Registration Failed ** Err.Number (HRESULT) = 0x1AD Err.Description = Act...

Problems with 'Names'
I am using MS Excel 2007. I have a series of linked files - 1 main 'parent' file with 5 'children' files. The parent file contains 'names' which are used in all the children files (eg..titles across columns, drop down boxes/lists (referencing name ranges) etc) I need to find and replace some of the external links in the files with new file extensions - XLSM and XLSX. I can find and replace as required in all the formulas which work, however the 'names' in the file still refer to the .XLSM file. If you search the entire workbook .XLSM cann...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

FROM display name
In Exchange 5.5, what is it using for the displayed FROM name on Internet Mail. For example, I want "John Smith" to show as "John Smith- Sales" I tried changing the display name in exchange admin, but that didn't change it. Can you change that without making a new mail box? On Tue, 6 Jun 2006 11:39:40 -0600, "John Smith" <someone@microsoft.com> wrote: >In Exchange 5.5, what is it using for the displayed FROM name on Internet >Mail. For example, I want "John Smith" to show as "John Smith- Sales" I >tried changing the ...

How can I select the first blank cell
Hi all, please help. I have to add every day new data (number of rows is various) to existing data sheet. I suppose to use macro and run that automatically at night. As I have to add it to table where number of rows will groe up, I need to start copying new data to the first blank cell in the first column. How can I force macro to find this blank cell when the position will shift? Many thanks, Valdemar Find the end of a list: here are a couple different ways: this will select the cell. it's not a good practice to select, but just used as an illustration here. range...

Must Enter domain name on OWA 2003 Front End?
I noticed two things happen if you do *not* use your domain name when you log onto the OWA 2003 Front End server: 1. When you use the Logout button you get a prompt for your username and password again 2. When you try to open a link within an email message you are prompted for your username and password, and then you must use your domain\username or else the link will never open. Q. Is this because our Internet Mail domain name and our AD domain name are different? I thought the instructions from Microsoft said you could set a default domain, but the options were only for my email domain...

Auto Fill display name
When I type a name in the to: field it will not recognize John Doe but it will recognize doe, john before I upgraded from 2000 to 2003 it would recognize John Doe. I have been unable to find any setting that would fix this. What you claim is not possible. Outlook 2000 did not even have an autocompletion feature when typing in the To: field. Only Outlook 2002 and 2003 do, and they use a cache of names created from messages you have sent, not your Contacts. -- Russ Valentine [MVP-Outlook] "HTS" <anonymous@discussions.microsoft.com> wrote in message news:572f01c48a35$501...

Extracting data to make mailing list
My neighbour has a .PUBS name and address doc. The doc is 10 pages long. Each page of the document is a table with 10 rows and 3 columns. Each cell is name on top the line followed by 2 or 3 lines of address. She then just cuts the full sheet Avery into mailing labels. This has become too difficult to manage, sort, filter, etc. Anyone have a suggestion how to extract this data so I can make a Publisher mailing list? Thanks. -- Ross try bringing it into Excel...you'll some "massaging" to do. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Ross"...

Contacts Sorted by Last Name
Hi, I recently imported my contacts from an older version of outlook into Outlook 2003. When I create a new message and click the "To" button to select my contacts. When I type in the name, I used to type in the letters of the last name and the name would come up. For example, John Smith. I would type SM and John Smith would come up. Now this isn't possible. I have to type in "John Sm". Any ideas how to change this. I would expect that. You have a new autocompletion/autoresolution cache now, so your old entries aren't there. After your repopulate the ca...

MID formula not extracting what I want.
I'm trying to extract the date from a cell using the MID formula and all I'm getting is some weird number that does not seem to make sense. I have tried formatting the target cell but still no change. Any clues about it? How can I do this? 1/13/2006 12:16:29 AM =MID(E5,1,9) 38730.011 -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860 View this thread: http://www.excelforum.com/showthread.php?threadid=501062 Hi That's because Excel stores dates as...