Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Evaluate a CHAR value in a CHARINDEX function
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GPSPOW
Starting Member

USA
5 Posts

Posted - 12/23/2012 :  13:33:53  Show Profile  Reply with Quote
I am creating a RIGHT function extract of data from a field called 'name' that looks like this:

ABS.PAT.calendar.two

I want to send the results to a field call 'pos' for the data 2 positions to the right of the "T" in "PAT", "calendar.two". I know the CHAR value is 84 for the letter "T".

So far I have a code statement as follows:

RIGHT(name,CHARINDEX("T",REVERSE(name),1)-1) as pos

The goal is to make where the "T" is, to be something like ">CHAR(89)", because I want to be able to fetch the data to the right of all capital letters.

Thanks


Glen

jimf
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 12/23/2012 :  19:08:57  Show Profile  Reply with Quote
If there's always 3 '.'s, this may be helpful

declare @str varchar(50) = 'ABS.PAT.calendar.two'
select PARSENAME(@str,4),PARSENAME(@str,3),PARSENAME(@str,2),PARSENAME(@str,1)

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

GPSPOW
Starting Member

USA
5 Posts

Posted - 12/24/2012 :  00:52:46  Show Profile  Reply with Quote
The dbo.fields.name have data in it where there can be from 3 to possible 6 "."'s in it the string. The constant is that when the CAPITALIZED part of the string ends there is always a "." after it. I am trying to extract the part of the string after the "." that follows the last CAPITALIZED letter in the string.

My data could be as follows:

ABS.PAT.name
ABS.DRG.LIST.drg.code
BAR.PAT.account.number

Any suggestions?

Thanks

Glen

Glen
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 12/24/2012 :  01:07:44  Show Profile  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083&SearchTerms=fnFilterString


KH
Time is always against us

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/24/2012 :  02:12:28  Show Profile  Reply with Quote
Create this function.. then run against your table


GO
CREATE FUNCTION udf_LastPosOfCapLetter (@String VARCHAR(500)) 
RETURNS int
AS 
BEGIN 
   DECLARE @return VARCHAR(50)
   DECLARE @position INT 

   SET @position = 1 

   WHILE @position <= DATALENGTH(@string) 
   BEGIN 
		DECLARE @Pos int = ASCII(SUBSTRING(@string, @position, 1))
       IF @Pos BETWEEN 65 AND 90 OR @Pos =46
           SELECT @return = 0 
       ELSE 
       BEGIN
           SELECT @return = 1 
           BREAK
       END

       IF @Return <> 1 
           SET @position = @position + 1 
	END 
	RETURN @position-1
END
GO
--SELECT dbo.udf_LastPosOfCapLetter('ABS.PAT.name')
GO
--test with your table.. just replace @tab with your table name and column name c1
DECLARE @tab TABLE(c1 VARCHAR(30) )
insert into @tab VALUES('ABS.PAT.calendar.two'), ('ABS.PAT.name'),('ABS.DRG.LIST.drg.code'),('BAR.PAT.account.number')
SELECT RIGHT(c1, LEN(c1)-  dbo.udf_LastPosOfCapLetter(c1)) FROM @tab
GO


--
Chandu
Go to Top of Page

GPSPOW
Starting Member

USA
5 Posts

Posted - 12/24/2012 :  15:20:04  Show Profile  Reply with Quote
Thanks.

It worked perfectly.

Glen

Glen
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/26/2012 :  00:00:31  Show Profile  Reply with Quote
quote:
Originally posted by GPSPOW

Thanks.
It worked perfectly.
Glen

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000