SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Special characters


Special characters

Author
Message
shashi_1409
shashi_1409
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 220
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
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42085 Visits: 14925
What do you mean by special characters?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
shashi_1409
shashi_1409
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 220
any characters other than [a-z],[A-Z],[0-9]
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42085 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
shashi_1409
shashi_1409
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 220
great !!


Thanks Jack!
shashi_1409
shashi_1409
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 220
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]%'
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42085 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Garadin
Garadin
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6621 Visits: 4107
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
shashi_1409
shashi_1409
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 220
Thanks a lot!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search