No Wildcards Characters - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

No Wildcards Characters

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-26 : 07:13:37

Hi,
I want to find index of a character which same with one of following characters:
* # ~ !

So I should use this:

patindex ('%[*#~!]%', string_value)

That's okay but for this list: [ ] ^ _ %
I have problem.

patindex ('%[[]^_%]%', string_value) <<<=== WRONG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 07:27:37

see


SELECT *
FROM
(
SELECT '[ ] ^ _ %' AS string_value UNION ALL
SELECT '[ ] ^ _ %anjhd' UNION ALL
SELECT 'jkhjkhnb[ ] ^ _ %' UNION ALL
SELECT 'hjjhgjhg[ ] ^ _ %anjhd' 
)t
WHERE patindex ('%[[ ]] ^ _ [%]', string_value) >0


output
--------------------------------------
string_value
--------------------------------------
[ ] ^ _ %
jkhjkhnb[ ] ^ _ %

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 07:35:42

another way using ESCAPE


SELECT *
FROM
(
SELECT '[ ] ^ _ %' AS string_value UNION ALL
SELECT '[ ] ^ _ %anjhd' UNION ALL
SELECT 'jkhjkhnb[ ] ^ _ %' UNION ALL
SELECT 'jkhjkhnb[ ][ ^ _ %' UNION ALL
SELECT 'hjjhgjhg[ ] ^ _ %anjhd' 
)t
WHERE string_value LIKE '%|[ ] ^ _ |%' ESCAPE '|'


output
-------------------------------
string_value
-------------------------------
[ ] ^ _ %
jkhjkhnb[ ] ^ _ %

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-26 : 08:57:25

No no!
I want return index of first characters witch matched with one of following characters:
[
]
%
^

for example


Sample Data      Result
asdl[ sdf    >>> 5
] asf sdf    >>> 1
asdfhsdaf    >>> 0
as[] [][]    >>> 3
a^asf[as]    >>> 2

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-26 : 09:05:21

logically same with this:


select (select min(v) 
          from
                (
                     select patindex('%[[]%',data) union 
                     select patindex('%]%', data) union
                     select patindex('%[%]%', data)
               )d(v)
          where v > 0
        ) as ix
from
(
values ('asdfd[ ][][')
)t(data)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 11:27:26

do you mean this?


SELECT *
FROM 
(
SELECT 'wrwrwewe%anjhd' AS string_value UNION ALL
SELECT 'jkhjkhnb[ ]m,n,m' UNION ALL
SELECT 'jkhjkhnb' UNION ALL
SELECT 'hjjhg_jhg^' UNION ALL
SELECT 'jhgjg$yutuu'
)t
WHERE string_value LIKE '%[$[ $]$^_$%]%' ESCAPE '$'

output
----------------------------
string_value
----------------------------
wrwrwewe%anjhd
jkhjkhnb[ ]m,n,m
hjjhg_jhg^

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-26 : 12:10:05

NO!
I need position of pattern at string values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-26 : 12:11:51

so if you've more than one character coming in a string how do you want output to come?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources