July 6, 2012 at 12:17 pm
one letter
July 6, 2012 at 12:22 pm
challapal (7/6/2012)
string starts with 3 digit same number Example 111Y,333Y, 555 H etcstring starts with NULL
string that starts with sequential digits Example 123g, 567jku
string that starts with sequential alphabets example ABCGHHJK, DEFHJUUJ, IJKL B etc
string only one character (digit or letter)
string only has same character repeated 3 times except for OOO
string only has three characters, 1 digit and two letters or 2 digit one letter
string only that has two characters one digit and one letter
Not sure what you are looking for. Are you trying to identify (n)varchar fields that have these characteristics?
This is a pretty random list but let's tackle a couple of super easy ones.
string starts with NULL
Read up about what NULL is and then ask yourself if a string can start with a NULL.
string only one character (digit or letter)
datalength
This list looks a lot like homework from a professor that is going to teach horrible things like using a cursor to determine this stuff.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 6, 2012 at 3:33 pm
Lookup the following keywords in Books Online and give it a try yourself. If you post what you've actually tried, folks might be a little more willing to help you.
LIKE
SUBSTRING()
CHAR()
ASCII()
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 4:24 pm
asdfas
July 6, 2012 at 5:01 pm
I started with the first few:
Create Sample Data:
CREATE table x(Value varchar(20))
INSERT INTO x VALUES
('a'),
('A'),
('NULL'),
('NULL xxx'),
('113Y'),
('111Y'),
('1')
-- 1. string starts with 3 digit same number Example 111Y,333Y, 555 H etc
-- a. Mehtod 1
SELECT * FROM x
WHERE LEN(Value) > 3 AND
(
value LIKE '111%' OR
value LIKE '222%' OR
value LIKE '333%' OR
value LIKE '444%' OR
value LIKE '555%' OR
value LIKE '666%' OR
value LIKE '777%' OR
value LIKE '888%' OR
value LIKE '999%' OR
value LIKE '000%'
)
-- b. Method 2
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
AND CHARINDEX(VALUE,1) = CHARINDEX(VALUE,2)
AND CHARINDEX(VALUE,2) = CHARINDEX(VALUE,3)
-- 2. string starts with NULL
-- a. to get ANYTHING beginning with 'NULL' including the string 'NULL' (with nothing after)
SELECT * FROM x
WHERE value LIKE 'NULL%'
-- b. to get strings beginning with 'NULL' excluding the string 'NULL'
SELECT * FROM x
WHERE LEN(Value) > 4
AND value LIKE 'NULL%'
-- 3. string that starts with sequential digits Example 123g, 567jku
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
-- 4. string only one character (digit or letter)
SELECT * FROM x
WHERE LEN(Value) = 1 AND
(Value LIKE '[0-9]' OR Value LIKE '[a-z]')
-- Itzik Ben-Gan 2001
July 6, 2012 at 5:25 pm
XMLSQLNinja (7/6/2012)
I started with the first few:Create Sample Data:
CREATE table x(Value varchar(20))
INSERT INTO x VALUES
('a'),
('A'),
('NULL'),
('NULL xxx'),
('113Y'),
('111Y'),
('1')
-- 1. string starts with 3 digit same number Example 111Y,333Y, 555 H etc
-- a. Mehtod 1
SELECT * FROM x
WHERE LEN(Value) > 3 AND
(
value LIKE '111%' OR
value LIKE '222%' OR
value LIKE '333%' OR
value LIKE '444%' OR
value LIKE '555%' OR
value LIKE '666%' OR
value LIKE '777%' OR
value LIKE '888%' OR
value LIKE '999%' OR
value LIKE '000%'
)
-- b. Method 2
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
AND CHARINDEX(VALUE,1) = CHARINDEX(VALUE,2)
AND CHARINDEX(VALUE,2) = CHARINDEX(VALUE,3)
-- 2. string starts with NULL
-- a. to get ANYTHING beginning with 'NULL' including the string 'NULL' (with nothing after)
SELECT * FROM x
WHERE value LIKE 'NULL%'
-- b. to get strings beginning with 'NULL' excluding the string 'NULL'
SELECT * FROM x
WHERE LEN(Value) > 4
AND value LIKE 'NULL%'
-- 3. string that starts with sequential digits Example 123g, 567jku
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
-- 4. string only one character (digit or letter)
SELECT * FROM x
WHERE LEN(Value) = 1 AND
(Value LIKE '[0-9]' OR Value LIKE '[a-z]')
I'm not sure what the intent is here but a good number of the answers here are incorrect and one could be optimized a whole lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 5:29 pm
challapal (7/6/2012)
I have tried below for identifying account name with no alpha numeric having only special characters like below
That's a totally different question. What happened to the first ones you asked? The solutions from those will lead to the solution here.
I'll give you a hint... up to you to figure out the rest. Lookup LIKE and follow your nose after that.
'%[^0-9a-zA-Z]%' COLLATE Latin1_General_BIN
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2012 at 10:50 pm
Hi Thanks a lot for helping me , most of the queries helped me, below one is not working for string that starts
with consecutive numbers
-- 3. string that starts with sequential digits Example 123g, 567jku
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
I am able to solve most,I am stuck with 3 queries below
--------------------------------------------------------------------------------------
string that starts with sequential digits Example 123g, 567jku
string that starts with sequential alphabets Example abcg, mnojku, efg tty
string that has three characters, 1 digit and two letters or 2 digit one letter
July 7, 2012 at 9:50 am
challapal (7/6/2012)
Hi Thanks a lot for helping me , most of the queries helped me, below one is not working for string that startswith consecutive numbers
-- 3. string that starts with sequential digits Example 123g, 567jku
SELECT * FROM x
WHERE ISNUMERIC(LEFT(value,3)) = 1
I am able to solve most,I am stuck with 3 queries below
--------------------------------------------------------------------------------------
string that starts with sequential digits Example 123g, 567jku
string that starts with sequential alphabets Example abcg, mnojku, efg tty
string that has three characters, 1 digit and two letters or 2 digit one letter
Post the answers for the questions that you've currently solved so we can check them for you. Then, I'll show you how to solve the 3 above.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2012 at 8:37 pm
CREATE FUNCTION remove_duplicate_characters (@string varchar(100))
returns varchar(100)
BEGIN
DECLARE @result varchar(100)
set @result=''
select @result=@result+min(substring(@string ,number,1)) from
(
select number from master..spt_values where type='p' and number between 1 and len(@string )) as t group by
substring(@string,number,1)order by min(number)
return @result
END
SELECT A.NAME,dbo.remove_duplicate_characters(A.NAME) NO_DUPS,LEN(dbo.remove_duplicate_characters(A.NAME)) AS LENGTH
FROM mytable A WHERE LEN(dbo.remove_duplicate_characters(A.NAME))=1 and dbo.remove_duplicate_characters(A.NAME)<>'?'
and LEN(a.name)>1
Name have same letter with or without space
------------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM mytable A WHERE LEN(dbo.remove_duplicate_characters(A.NAME))=1
and dbo.remove_duplicate_characters(A.NAME)<>'?'
and LEN(a.name)>1
Name contains GFD
-------------------------------------------------------------------------------------------------------
select COUNT(*) from mytable where name like '%gfd%'
Name contains No Name with or with no space
-------------------------------------------------------------------------------------------------------
Select cast(replace(NAME,' ','') as char(100)),NAME from mytable
WHERE cast(replace(NAME,' ','') as char(100)) like 'NoName%'
or cast(replace(NAME,' ','') as char(100)) like 'noname%'
Name contains No One with or with no space
-------------------------------------------------------------------------------------------------------
Select cast(replace(NAME,' ','') as char(100)),NAME from mytable
WHERE cast(replace(NAME,' ','') as char(100)) like 'NoOne%'
or cast(replace(NAME,' ','') as char(100)) like 'noone%'
Name start with SDF
-------------------------------------------------------------------------------------------------------
select COUNT(*) from mytable where name like 'sdf%'
Name start with ASDF
-------------------------------------------------------------------------------------------------------
select COUNT(*) from mytable where name like 'ASDF%'
Name start with DFA
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'DFA%'
Name start with DFD
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'DFD%'
Name start with DFG
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'DFG%'
Name start with DFS
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'DFS%'
Name start with DSF
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'DSF%'
Name start with FGH
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'FGH%'
Name start with NONE
-------------------------------------------------------------------------------------------------------
select * from W_SBL_S_ORG_EXT where name like 'NONE%'
Name starts with NULL or null
------------------------------------------------------------------------------------------------------
select a.name,LEN(a.name) as LENGTH from mytable a where
((a.name like 'NULL%') or (a.name like 'null%') ) AND LEN(a.name)=4
Name start with ADS
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'ADS%' AND NAME NOT LIKE 'ADSK N%'
Name start with SADF
-------------------------------------------------------------------------------------------------------
select * from mytable where name like 'SADF%'
Only one character (digit or letter)
-----------------------------------------------------------------
SELECT * FROM mytable
WHERE LEN(name) = 1 AND
(name LIKE '[0-9]' OR name LIKE '[a-z]')
String that start with 3 digit same number
----------------------------------------------------------------------
SELECT * FROM mytable
WHERE LEN(NAME) >= 3 AND
(
NAME LIKE '111%' OR
NAME LIKE '222%' OR
NAME LIKE '333%' OR
NAME LIKE '444%' OR
NAME LIKE '555%' OR
NAME LIKE '666%' OR
NAME LIKE '777%' OR
NAME LIKE '888%' OR
NAME LIKE '999%' OR
NAME LIKE '000%'
)
String that start with 3 consequetive letters
----------------------------------------------------------------------
SELECT * FROM mytable
WHERE LEN(NAME) >= 3 AND
(
NAME LIKE 'ABC%' OR
NAME LIKE 'BCD%' OR
NAME LIKE 'CDE%' OR
--NAME LIKE 'DEF%' OR
NAME LIKE 'EFG%' OR
NAME LIKE 'FGH%' OR
--NAME LIKE 'GHI%' OR
NAME LIKE 'HIJ%' OR
NAME LIKE 'IJK%' OR
NAME LIKE 'JKL%' OR
--NAME LIKE 'KLM%' OR
NAME LIKE 'LMN%' OR
NAME LIKE 'MNO%' OR
NAME LIKE 'NOP%' OR
NAME LIKE 'OPQ%' OR
NAME LIKE 'PQR%' OR
NAME LIKE 'QRS%' OR
NAME LIKE 'RST%' OR
--NAME LIKE 'STU%' OR
NAME LIKE 'TUV%' OR
NAME LIKE 'UVW%' OR
NAME LIKE 'VWX%' OR
NAME LIKE 'WXY%' OR
NAME LIKE 'XYZ%'
)
String that start with 3 consequetive numbers
----------------------------------------------------------------------
SELECT * FROM mytable
WHERE LEN(NAME) >= 3 AND
(
NAME LIKE '123%' OR
NAME LIKE '234%' OR
NAME LIKE '345%' OR
NAME LIKE '456%' OR
NAME LIKE '567%' OR
NAME LIKE '678%' OR
NAME LIKE '789%'
)
String that start with 3 char same letter
----------------------------------------------------------------------
SELECT * FROM mytable
WHERE LEN(NAME) >= 3 AND
(
NAME LIKE 'AAA%' OR
NAME LIKE 'BBB%' OR
NAME LIKE 'CCC%' OR
NAME LIKE 'DDD%' OR
NAME LIKE 'EEE%' OR
NAME LIKE 'FFF%' OR
NAME LIKE 'GGG%' OR
NAME LIKE 'HHH%' OR
NAME LIKE 'III%' OR
NAME LIKE 'JJJ%' OR
NAME LIKE 'KKK%' OR
NAME LIKE 'LLL%' OR
NAME LIKE 'MMM%' OR
NAME LIKE 'NNN%' OR
--NAME LIKE 'OOO%' OR
NAME LIKE 'PPP%' OR
NAME LIKE 'QQQ%' OR
NAME LIKE 'RRR%' OR
NAME LIKE 'SSS%' OR
NAME LIKE 'TTT%' OR
NAME LIKE 'UUU%' OR
NAME LIKE 'VVV%' OR
NAME LIKE 'WWW%' OR
NAME LIKE 'XXX%' OR
NAME LIKE 'YYY%' OR
NAME LIKE 'ZZZ%'
)
July 7, 2012 at 9:03 pm
Only SQL i am struggling with is below now
-------------------------------------------------------------------------------------------------------------------------
string with ONLY special characters (Avoid a-z 0-9 and Double Bytes ,Chinese and Japanese koren russian any other char too)
for that i wrote a fucntion below which is not working need help
CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)
BEGIN
IF @InputString IS NULL
RETURN NULL
DECLARE @OutputString VARCHAR(8000)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@InputString, @p, 1))
IF @C BETWEEN 48 AND 57
OR @C BETWEEN 65 AND 90
OR @C BETWEEN 97 AND 122
--OR @C = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@OutputString) = 0
RETURN NULL
RETURN @OutputString
END
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')
SELECT dbo.RemoveSpecialChars ('(?)??????????') COLLATE Latin1_General_BIN -not working for this these are not special char dont want these to be considered
as special char
SELECT dbo.RemoveSpecialChars ('???')--working for this
SELECT dbo.RemoveSpecialChars ('---')--working for this
July 7, 2012 at 9:03 pm
Only SQL i am struggling with is below now
-------------------------------------------------------------------------------------------------------------------------
string with ONLY special characters (Avoid a-z 0-9 and Double Bytes ,Chinese and Japanese koren russian any other char too)
for that i wrote a fucntion below which is not working need help
CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)
BEGIN
IF @InputString IS NULL
RETURN NULL
DECLARE @OutputString VARCHAR(8000)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@InputString, @p, 1))
IF @C BETWEEN 48 AND 57
OR @C BETWEEN 65 AND 90
OR @C BETWEEN 97 AND 122
--OR @C = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@OutputString) = 0
RETURN NULL
RETURN @OutputString
END
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')
SELECT dbo.RemoveSpecialChars ('(?)??????????') COLLATE Latin1_General_BIN -not working for this these are not special char dont want these to be considered
as special char
SELECT dbo.RemoveSpecialChars ('???')--working for this
SELECT dbo.RemoveSpecialChars ('---')--working for this
July 7, 2012 at 9:05 pm
Only SQL i am struggling with is below now
-------------------------------------------------------------------------------------------------------------------------
string with ONLY special characters (Avoid a-z 0-9 and Double Bytes ,Chinese and Japanese koren russian any other char too)
for that i wrote a fucntion below which is not working need help
CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)
BEGIN
IF @InputString IS NULL
RETURN NULL
DECLARE @OutputString VARCHAR(8000)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@InputString, @p, 1))
IF @C BETWEEN 48 AND 57
OR @C BETWEEN 65 AND 90
OR @C BETWEEN 97 AND 122
--OR @C = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@OutputString) = 0
RETURN NULL
RETURN @OutputString
END
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')
SELECT dbo.RemoveSpecialChars ('(?)??????????') COLLATE Latin1_General_BIN -not working for this these are not special char dont want these to be considered
as special char
SELECT dbo.RemoveSpecialChars ('???')--working for this
SELECT dbo.RemoveSpecialChars ('---')--working for this
July 7, 2012 at 9:06 pm
Only SQL i am struggling with is below now
-------------------------------------------------------------------------------------------------------------------------
string with ONLY special characters (Avoid a-z 0-9 and Double Bytes ,Chinese and Japanese koren russian any other char too)
for that i wrote a fucntion below which is not working need help
CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR(8000) )
RETURNS VARCHAR(8000)
BEGIN
IF @InputString IS NULL
RETURN NULL
DECLARE @OutputString VARCHAR(8000)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @C INT
SET @C = ASCII(SUBSTRING(@InputString, @p, 1))
IF @C BETWEEN 48 AND 57
OR @C BETWEEN 65 AND 90
OR @C BETWEEN 97 AND 122
--OR @C = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END
IF LEN(@OutputString) = 0
RETURN NULL
RETURN @OutputString
END
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')
SELECT dbo.RemoveSpecialChars ('(?)??????????') COLLATE Latin1_General_BIN -not working for this these are not special char dont want these to be considered
as special char
SELECT dbo.RemoveSpecialChars ('???')--working for this
SELECT dbo.RemoveSpecialChars ('---')--working for this
July 8, 2012 at 11:52 am
challapal (7/7/2012)
Only SQL i am struggling with is below now-------------------------------------------------------------------------------------------------------------------------
SELECT dbo.RemoveSpecialChars ('This string contains special chracters:/ Which * & we % need @ to #remove')
Most of your post is a bit confusing to me. I suspect it's just the "language barrier" taking a toll here. With that in mind, I've isolated one of your examples because it most closely matches what your code appears to be trying to do.
With that thought in mind, let's solve that problem with the quickest code I know. A lot of my friends on this forum will likely drop their jaws in absolute disbelief because the solution isn't set based. The code I use for this actually uses a Scalar UDF and a While Loop and will soundly beat set based versions IF the number of "special characters" to be removed is small compared to the overall content of the string to be cleaned.
CREATE FUNCTION dbo.RemoveSpecialCharacters
/**********************************************************************************************************************
Purpose:
This Scalar function removes all special characters except spaces, returns blank results as NULL, and converts
multiple adjacent spaces to single spaces.
Usage:
SELECT dbo.RemoveSpecialCharacters(@pSomeString);
SELECT dbo.RemoveSpecialCharacters(SomeStringColumn)
FROM dbo.SomeTable;
Reference:
George Mastros post
Revision History:
Rev 00 - 08 Jul 2012 - Jeff Moden
- Redaction of code to meet the current requirements stated in the "Purpose" above.
**********************************************************************************************************************/
--===== Declare the I/O for this function
(@pSomeString VARCHAR(8000))
RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
--===== Delete all special characters except spaces.
WHILE PATINDEX('%[^a-zA-Z0-9 ]%',@pSomeString COLLATE Latin1_General_BIN) > 0
SET @pSomeString = STUFF(@pSomeString,PATINDEX('%[^a-zA-Z0-9 ]%',@pSomeString COLLATE Latin1_General_BIN),1,'');
--===== Convert empty strings to NULL, replace multiple adjacent spaces with just one, and return the function value.
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NULLIF(@pSomeString,''),' ',' '),' ',' '),' ',' '),' ',' '),' ',' ');
END
;
GO
Although I modified it for performance a bit and I also modified it to reduce multiple spaces to a single space, the credit for this little piece of computational heaven goes to a fellow by the name of George Mastros. A similar piece of his work may be found at the following link...
If we use it the the example string for your post...
SELECT dbo.RemoveSpecialCharacters('This string contains special chracters:/ Which * & we % need @ to #remove')
;
... we get the following for a return...
This string contains special chracters Which we need to remove
Now, the big question is, [font="Arial Black"]do you actually know what each piece of the code is doing?[/font] Considering the nature of the other simple questions you posted and that you're new to SQL, I would guess the answer is "probably not".
With that thought in mind, I strongly suggest you curl up with a copy of Books Online (The "help" system that comes with SQL Server, is available online, and can be downloaded separately), lookup things like WHILE, PATINDEX, COLLATE, REPLACE, and WILDCARDs and teach yourself something new. If you do that, you'll not only excel in school, but in your career, as well. Always remember that "A Developer must not guess... a Developer must KNOW!".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply