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
 Combining @variables and select queries

Author  Topic 

GPSPOW
Starting Member

5 Posts

Posted - 2012-12-27 : 21:04:55
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-28 : 00:03:35
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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-28 : 01:38:21
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
   

- Advertisement -