SQL Server Forums
Profile | Register | 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
 New Topic  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
2869 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
17587 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
2202 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
2202 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  
 New 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