July 7, 2012 at 9:09 am
Can someone guide me how to write these queries below in SQLSERVER
i am a new developer
--------------------------------------------------------------------------------------
string that starts with consecutive digits Example 123g, 567jku
string that starts with consecutive 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:34 am
challapal (7/7/2012)
Can someone guide me how to write these queries below in SQLSERVERi am a new developer
--------------------------------------------------------------------------------------
string that starts with consecutive digits Example 123g, 567jku
string that starts with consecutive alphabets Example abcg, mnojku, efg tty
string that has three characters, 1 digit and two letters or 2 digit one letter
Based on your question these would be the answers. Not sure if you are looking for something else
Select 'abcg', 'mnojku', 'efg', 'tty'
Select '123g', '567jku'
Select '1ab','12a'
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2012 at 1:35 pm
challapal (7/7/2012)
Can someone guide me how to write these queries below in SQLSERVERi am a new developer
--------------------------------------------------------------------------------------
string that starts with consecutive digits Example 123g, 567jku
string that starts with consecutive alphabets Example abcg, mnojku, efg tty
string that has three characters, 1 digit and two letters or 2 digit one letter
This isn't right. You already have a thread open on these. All you're going to do is tick people trying to help you off. Stick to the original thread so that the answers aren't all divided up. Let's also not forget that these are homework problems and, from what I've seen, you've not really tried anything on your own. 😉
The link to the other thread is http://www.sqlservercentral.com/Forums/Topic1326271-1292-1.aspx#bm1326384
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2012 at 9:00 pm
HI
I have solved most of queries below getting idea from sample queries given by "SSC-Dedicated"
thanks a lot
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%'
)
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply