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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Evaluate a CHAR value in a CHARINDEX function

Author  Topic 

GPSPOW
Starting Member

5 Posts

Posted - 2012-12-23 : 13:33:53
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-23 : 19:08:57
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

5 Posts

Posted - 2012-12-24 : 00:52:46
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)

17689 Posts

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


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-24 : 02:12:28
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

5 Posts

Posted - 2012-12-24 : 15:20:04
Thanks.

It worked perfectly.

Glen

Glen
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 00:00:31
quote:
Originally posted by GPSPOW

Thanks.
It worked perfectly.
Glen

Welcome

--
Chandu
Go to Top of Page
   

- Advertisement -