Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Special characters Expand / Collapse
Author
Message
Posted Tuesday, November 24, 2009 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
Hi, I am trying to list usernames with special characters from user table.

I have tried using like operator, but I dont have the complete list of special characters . so I would really appreciate if some one can help me out with the query.

or

help me with the query that lists all special characters.

thanks



Post #824139
Posted Tuesday, November 24, 2009 2:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
What do you mean by special characters?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #824154
Posted Tuesday, November 24, 2009 2:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
any characters other than [a-z],[A-Z],[0-9]
Post #824169
Posted Tuesday, November 24, 2009 3:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
How about something like this:

/*
Set up test table and data
*/
IF OBJECT_ID('dbo.#test') IS NOT NULL
BEGIN
DROP TABLE #test
END
CREATE TABLE #test
(
test_col VARCHAR(10)
)

DECLARE @int INTEGER,
@string VARCHAR(10)




SET @int = 0

WHILE @int <=223
BEGIN
IF LEN(@string) < 10 OR @string IS NULL
BEGIN
SET @string = ISNULL(@string, '') + CHAR(FLOOR(RAND(@int) * @int + 1))
END
ELSE
Begin
INSERT INTO #test
SELECT
@string

SET @string = ''
END

SET @int = @int + 1

END
/*
Return all the rows
*/
SELECT
*
FROM
#test AS T

/*
Return the rows that return non-alphanumeric characters.
*/
SELECT
*
FROM
#test AS T
WHERE
test_col LIKE '%[^a-z]%' AND
test_col LIKE '%[^0-9]%' AND
test_col LIKE '%[^A-Z]%'






Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #824179
Posted Tuesday, November 24, 2009 3:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
great !!


Thanks Jack!
Post #824185
Posted Tuesday, November 24, 2009 3:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
Jack I am sorry it worked with your example, but when I worked with my test data its not working.. I should return only 1st 5 records but its returning more..could you please help me resolve this issue

here is my test data



IF OBJECT_ID('dbo.#frUser_Copy') IS NOT NULL
BEGIN
DROP TABLE #frUser_Copy
END
CREATE TABLE #frUser_Copy
(
UserName VARCHAR(25)
)
Go
INSERT INTO #frUser_Copy([UserName])VALUES ('shashi>')
INSERT INTO #frUser_Copy([UserName])VALUES ('sh>shashi')
INSERT INTO #frUser_Copy([UserName])VALUES ('shashi"&')
INSERT INTO #frUser_Copy([UserName])VALUES ('shash?34')
INSERT INTO #frUser_Copy([UserName])VALUES ('sh&&?34')
INSERT INTO #frUser_Copy([UserName])VALUES ('22jsnell')
INSERT INTO #frUser_Copy([UserName])VALUES ('71jsnell')
INSERT INTO #frUser_Copy([UserName])VALUES ('71rowens')
INSERT INTO #frUser_Copy([UserName])VALUES ('99jsnell')
INSERT INTO #frUser_Copy([UserName])VALUES ('abcrep')
INSERT INTO #frUser_Copy([UserName])VALUES ('abcuser')
INSERT INTO #frUser_Copy([UserName])VALUES ('acctrep01')
INSERT INTO #frUser_Copy([UserName])VALUES ('acctrep1')
INSERT INTO #frUser_Copy([UserName])VALUES ('Admin')
INSERT INTO #frUser_Copy([UserName])VALUES ('Andrew')
INSERT INTO #frUser_Copy([UserName])VALUES ('ANewUser')
INSERT INTO #frUser_Copy([UserName])VALUES ('asingh')
INSERT INTO #frUser_Copy([UserName])VALUES ('askillmeyer')
INSERT INTO #frUser_Copy([UserName])VALUES ('B2BFiler10')
INSERT INTO #frUser_Copy([UserName])VALUES ('BEHenri')
INSERT INTO #frUser_Copy([UserName])VALUES ('BEOnlineAcct01')
INSERT INTO #frUser_Copy([UserName])VALUES ('bermudafiler')
INSERT INTO #frUser_Copy([UserName])VALUES ('BEtesting')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhanup')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhartmere')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhetju')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhetjun')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhetjunk')
INSERT INTO #frUser_Copy([UserName])VALUES ('bhetland')
INSERT INTO #frUser_Copy([UserName])VALUES ('BillBradley')
INSERT INTO #frUser_Copy([UserName])VALUES ('bmorgan')
INSERT INTO #frUser_Copy([UserName])VALUES ('bpersha')
INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlier')
INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlier01')
INSERT INTO #frUser_Copy([UserName])VALUES ('ccharlierext')
INSERT INTO #frUser_Copy([UserName])VALUES ('cici11')
INSERT INTO #frUser_Copy([UserName])VALUES ('ClaudiaOrg')
INSERT INTO #frUser_Copy([UserName])VALUES ('cmylavarapu')
INSERT INTO #frUser_Copy([UserName])VALUES ('crcharlier')
INSERT INTO #frUser_Copy([UserName])VALUES ('CTCorp')
INSERT INTO #frUser_Copy([UserName])VALUES ('CTCorpJR')
INSERT INTO #frUser_Copy([UserName])VALUES ('cvanroy')
INSERT INTO #frUser_Copy([UserName])VALUES ('cvanroy1')
INSERT INTO #frUser_Copy([UserName])VALUES ('ddiacont')
INSERT INTO #frUser_Copy([UserName])VALUES ('dmacd')
INSERT INTO #frUser_Copy([UserName])VALUES ('dmacdougall')
INSERT INTO #frUser_Copy([UserName])VALUES ('dmason')
INSERT INTO #frUser_Copy([UserName])VALUES ('dmasononline2')
INSERT INTO #frUser_Copy([UserName])VALUES ('dmasononline3')
INSERT INTO #frUser_Copy([UserName])VALUES ('dsingh')
INSERT INTO #frUser_Copy([UserName])VALUES ('ebarnard')
INSERT INTO #frUser_Copy([UserName])VALUES ('Elisa42')
INSERT INTO #frUser_Copy([UserName])VALUES ('eottesen')
INSERT INTO #frUser_Copy([UserName])VALUES ('ExternalFiler01')
INSERT INTO #frUser_Copy([UserName])VALUES ('externalrole')
INSERT INTO #frUser_Copy([UserName])VALUES ('FileOneAdmin')
INSERT INTO #frUser_Copy([UserName])VALUES ('FM14Test')
INSERT INTO #frUser_Copy([UserName])VALUES ('general')
INSERT INTO #frUser_Copy([UserName])VALUES ('gilligan')
INSERT INTO #frUser_Copy([UserName])VALUES ('guestuser')
INSERT INTO #frUser_Copy([UserName])VALUES ('hbali')
INSERT INTO #frUser_Copy([UserName])VALUES ('henrithomas')
INSERT INTO #frUser_Copy([UserName])VALUES ('HermeetBali123')
INSERT INTO #frUser_Copy([UserName])VALUES ('hthomas')
INSERT INTO #frUser_Copy([UserName])VALUES ('hthomas1')
INSERT INTO #frUser_Copy([UserName])VALUES ('internaluser')
INSERT INTO #frUser_Copy([UserName])VALUES ('jhenderson01')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjones')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesalt1')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesalternate')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesext2')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesexternal')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesexternal1')
INSERT INTO #frUser_Copy([UserName])VALUES ('jjonesformer')


SELECT
*
FROM
#frUser_Copy
WHERE
[UserName] LIKE '%[^a-z]%' AND
[UserName] LIKE '%[^0-9]%' AND
[UserName] LIKE '%[^A-Z]%'
Post #824196
Posted Tuesday, November 24, 2009 4:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
I had the general idea right, just the implementation wrong.

Try this:

SELECT 
*
FROM
#frUser_Copy
WHERE
[UserName] LIKE '%[^a-z0-9]%'

Unless you have a case-sensitive collation then the a-z will work for any alpha characters including upper-case




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #824210
Posted Tuesday, November 24, 2009 8:36 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
The AND NOT's can get pretty confusing, so just to explain this a bit more:

The problem is the logic of your where clause. Here's what you're saying(assuming case sensitivity doesn't matter for this explanation):

WHERE

[UserName] LIKE '%[^a-z]%' AND

This will pull in anything with a character other than a-z. So any string with a number passes this test.

[UserName] LIKE '%[^0-9]%' AND

This will pull in anything with a character other than 0-9, so anything with a letter passes this test.

[UserName] LIKE '%[^A-Z]%'

This will again, pull in anything with a character other than A-Z, so all strings with numbers pass this again.

Your end result? All strings with a non alphanumeric character(which is what you're after), as well as all strings with both a number and a letter, because these are evaluated separately.

Combining them like Jack shows above(or with a case sensitive collation using LIKE '%[^a-zA-Z0-9]%' looks for a character outside of *all* of those ranges at once, not each individually, which is what you're trying to do.

Also, if you were evaluating only a single character, your WHERE clause would be OK, because of the AND's. A single character would never pass all those tests if it was a number or letter, (letters fail the first or third, numbers fail the second, leaving you with what you want), the problem is that you're looking for *any* character in a series that passes for the entire string to pass, so any strings with individual characters that pass each criteria make it through.


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #824270
Posted Tuesday, November 24, 2009 9:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:03 AM
Points: 52, Visits: 187
Thanks a lot!!!
Post #824285
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse