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.
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.twoI 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 posThe 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.ThanksGlen |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-23 : 19:08:57
|
If there's always 3 '.'s, this may be helpfuldeclare @str varchar(50) = 'ABS.PAT.calendar.two'select PARSENAME(@str,4),PARSENAME(@str,3),PARSENAME(@str,2),PARSENAME(@str,1)JimEveryday I learn something that somebody else already knew |
|
|
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.nameABS.DRG.LIST.drg.codeBAR.PAT.account.numberAny suggestions?ThanksGlenGlen |
|
|
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] |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-24 : 02:12:28
|
Create this function.. then run against your tableGOCREATE FUNCTION udf_LastPosOfCapLetter (@String VARCHAR(500)) RETURNS intAS 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-1ENDGO--SELECT dbo.udf_LastPosOfCapLetter('ABS.PAT.name')GO--test with your table.. just replace @tab with your table name and column name c1DECLARE @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 @tabGO --Chandu |
|
|
GPSPOW
Starting Member
5 Posts |
Posted - 2012-12-24 : 15:20:04
|
Thanks.It worked perfectly.GlenGlen |
|
|
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 |
|
|
|
|
|
|
|