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
 Combining @variables and select queries
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GPSPOW
Starting Member

USA
5 Posts

Posted - 12/27/2012 :  21:04:55  Show Profile  Reply with Quote
I writing a View where I will take two field values from my table "fields" (ATTRIB and NAME). Within the field ATTRIB I will have to calculate the first position of the following characters (#,$,%,^,*,(,[,_,").

The best way I have found to determine where these characters reside within the ATTRIB field value is use the charindex() function and create 9 separate new fields.

What I want to do is store the resulting values in local variables, and then select the MIN value of the nine local variables (excluding zeroes) as a variable called @position to use in a substring or left function.

An example of the field ATTRIB value I am using is:

ABS.DRG.drg.name[ABS.PAT.number%4]

So in the above the '[' would have a position value of 17 and the '%' would have a position value of 32. Later I would use the position value of 17 to due my string extraction.

Thanks

Glen

Glen

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/28/2012 :  00:03:35  Show Profile  Reply with Quote
Put your symbols in one table, then do as follows
@SpecialSymbols is your symbols table
@TestData is your actual table

DECLARE @SpecialSymbols TABLE (Symbol CHAR(1) NOT NULL PRIMARY KEY)
INSERT @SpecialSymbols VALUES ('#'), ('$'), ('['), (']'), ('%'), ('^'),( '*'),('('),('_'), ('"')

DECLARE @TestData TABLE (StringToTest VARCHAR(100))
INSERT @TestData VALUES ('ABS.DRG.drg.name[ABS.PAT.number%4]'), ('.PAT.number%4]'), 
('test 1 [Using Square Brackets]'), ('[Test2@EmailAddress.com]'), ('No Special Symbols')
    
  SELECT  StringToTest, MIN(Position) pos
    FROM    @TestData
            OUTER APPLY
            ( SELECT  CHARINDEX(Symbol, StringToTest) [Position], Symbol
                FROM    @SpecialSymbols
            ) Symbols
    WHERE   Position > 0
	GROUP BY StringToTest

-- This is for getting all special character's position
DECLARE @SpecialSymbols TABLE (Symbol CHAR(1) NOT NULL PRIMARY KEY)
INSERT @SpecialSymbols VALUES ('#'), ('$'), ('['), (']'), ('%'), ('^'),( '*'),('('),('_'), ('"')

DECLARE @TestData TABLE (StringToTest VARCHAR(100))
INSERT @TestData VALUES ('ABS.DRG.drg.name[ABS.PAT.number%4]'), ('.PAT.number%4]'), 
('test 1 [Using Square Brackets]'), ('[Test2@EmailAddress.com]'), ('No Special Symbols')

;WITH CTE AS
(   SELECT  *, STUFF(StringToTest, Position, 1, ' ') [ReworkedString]
    FROM    @TestData
            OUTER APPLY
            (   SELECT  CHARINDEX(Symbol, StringToTest) [Position], Symbol
                FROM    @SpecialSymbols
            ) Symbols
    WHERE   Position > 0
    UNION ALL
    SELECT  StringToTest, Symbols.Position, Symbols.Symbol, STUFF(ReworkedString, Symbols.Position, 1, ' ') [ReworkedString]
    FROM    CTE
            OUTER APPLY
            (   SELECT  CHARINDEX(Symbol, ReworkedString) [Position], Symbol
                FROM    @SpecialSymbols
                WHERE   Symbol = CTE.Symbol
            ) Symbols
    WHERE   Symbols.Position > 0
)
SELECT  a.StringToTest, COALESCE(Location, '') [SpecialSymbolLocations]
FROM    @TestData a
        LEFT JOIN
        (   SELECT  DISTINCT
                    StringToTest,
                    -- THIS MERELY CONCATENATES ROWS INTO COLUMNS TO GET COMMA SEPARATED LIST
                    STUFF(( SELECT  ', ' + CONVERT(VARCHAR, Position)
                            FROM    CTE b
                            WHERE   a.StringToTest = b.StringToTest
                            ORDER BY Position
                            FOR XML PATH('')
                    ), 1, 2, '') [Location] 
            FROM    CTE a
        ) b
            ON a.StringToTest = b.StringToTest


--
Chandu

Edited by - bandi on 12/28/2012 01:48:55
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 12/28/2012 :  01:38:21  Show Profile  Reply with Quote
Alternate is as follows:
This SELECT will give the position of special character except 0-9, a-z, A-Z, and . (period)

DECLARE @TestData TABLE (StringToTest VARCHAR(100))
INSERT @TestData VALUES ('ABS.DRG.drg.name[ABS.PAT.number%4]'), ('.PAT.number%4]'),
('test 1 [Using Square Brackets]'), ('[Test2@EmailAddress.com]'), ('No Special Symbols')
SELECT PATINDEX('%[^0-z.]%', StringToTest) from @TestData

--
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.03 seconds. Powered By: Snitz Forums 2000