Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 15th, 2011, 08:48 PM
Authorized User
 
Join Date: Sep 2006
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default Finding Text in Select Statement

Hello All,

I have a list of patient names and I am trying to pull out the first letter in the name after BGirl or BBoy. In the first example the first letter after BBoy is 'A'. In the second example the first letter after BGirl is 'B' and in the third example the first letter after BBoy is null. Is there a way to select the letter or null in a select statement?

Thanks,
Tony

Code:
USE TempDB
GO

CREATE TABLE dbo.clientname
  (
    Name  VARCHAR(50)
  )

INSERT INTO clientname
(Name)
SELECT 'Smith, BBoyA Tonya' UNION ALL
SELECT 'Roberts, BGirlB Tammy' UNION ALL
SELECT 'Davis, BBoy Toni'
EDIT: I found out how.

select right(substring(name , 1 , charindex(' ', name) + 6),1)

Tony

Last edited by eusanpe; February 16th, 2011 at 12:13 AM.. Reason: Found the solution.
 
Old February 16th, 2011, 10:15 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Your solution returns space, B, T for me.

Try this instead...
Code:

 SELECT CASE          WHEN CHARINDEX('BBOY', Name) > 0
          THEN NULLIF(SUBSTRING(Name, CHARINDEX('BBoy' , Name) + 4, 1), ' ')          ELSE NULLIF(SUBSTRING(Name, CHARINDEX('BGirl', Name) + 5, 1), ' ')        END
  FROM dbo.ClientName
__________________
--Jeff Moden
 
Old February 16th, 2011, 10:16 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

As a side bar, the CODE formatter for this site really messes up code. It's one of the reasons I don't post here often.
__________________
--Jeff Moden
 
Old February 16th, 2011, 10:18 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Just trying a formatted post again to see what gives.

Code:
 SELECT CASE
            WHEN CHARINDEX('BBOY', Name) > 0
            THEN NULLIF(SUBSTRING(Name, CHARINDEX('BBoy' , Name) + 4, 1), ' ')
            ELSE NULLIF(SUBSTRING(Name, CHARINDEX('BGirl', Name) + 5, 1), ' ')
        END
   FROM dbo.ClientName
__________________
--Jeff Moden
 
Old February 16th, 2011, 10:19 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Note to self and anyone else who likes formatted code. You have to copy from SSMS into NotePad and then paste between the CODE tags to get proper space indentation. Of course, that removes all color but at least the code is readable that way.
__________________
--Jeff Moden
 
Old February 16th, 2011, 10:22 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by eusanpe View Post
Hello All,

I have a list of patient names and I am trying to pull out the first letter in the name after BGirl or BBoy. In the first example the first letter after BBoy is 'A'. In the second example the first letter after BGirl is 'B' and in the third example the first letter after BBoy is null. Is there a way to select the letter or null in a select statement?

Thanks,
Tony

Code:
USE TempDB
GO
 
CREATE TABLE dbo.clientname
  (
    Name  VARCHAR(50)
  )
 
INSERT INTO clientname
(Name)
SELECT 'Smith, BBoyA Tonya' UNION ALL
SELECT 'Roberts, BGirlB Tammy' UNION ALL
SELECT 'Davis, BBoy Toni'
EDIT: I found out how.

select right(substring(name , 1 , charindex(' ', name) + 6),1)

Tony
BWAA-HAAA!!! I just saw who posted this! Long time no see, Tony! Still working at the same site, I see.
__________________
--Jeff Moden
 
Old February 16th, 2011, 11:40 PM
Authorized User
 
Join Date: Sep 2006
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

Thanks Jeff...I will give it a go tomorrow.

I am at another facility now. I hope things are going well with you.

Take care,
Tony





Similar Threads
Thread Thread Starter Forum Replies Last Post
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Select Statement jmss66 VB How-To 3 March 13th, 2006 03:55 PM
Finding a string in the text muki XSLT 5 November 15th, 2005 11:20 PM
Finding records containing similar text salman Classic ASP Databases 3 November 30th, 2004 10:17 AM
Finding width of text jerimorris Javascript How-To 4 September 8th, 2003 02:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.