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
 Combining @variables and select queries
 New Topic  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
2217 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
2217 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  
 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.12 seconds. Powered By: Snitz Forums 2000