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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 No Wildcards Characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/26/2013 :  07:13:37  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/26/2013 :  07:27:37  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/26/2013 :  07:35:42  Show Profile  Reply with Quote
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/

Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/26/2013 :  08:57:25  Show Profile  Reply with Quote
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


Edited by - sigmas on 01/26/2013 08:58:35
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/26/2013 :  09:05:21  Show Profile  Reply with Quote
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)


Edited by - sigmas on 01/26/2013 09:05:43
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/26/2013 :  11:27:26  Show Profile  Reply with Quote
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/

Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/26/2013 :  12:10:05  Show Profile  Reply with Quote
NO!
I need position of pattern at string values.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/26/2013 :  12:11:51  Show Profile  Reply with Quote
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/

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