DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 9--------------------------------------------------
String Manipulation Techniques
By: srinivasma_exceldbp Date: February 27, 2014, 12:10 am
---------------------------------------------------------
--** Counting Occurrences of a Substring Within a String
declare
@str varchar(1000)
server',
@substr varchar(1000)
select (len(@str) - len(replace(@str, @substr, ''))) /
len(@substr);
go
--==============================================================
========
--** Check for exactly N occurences of a string
declare
@str varchar(1000)
server',
@substr varchar(1000)
@n
select case
when (len(@str) - len(replace(@str, @substr, ''))) /
len(@substr) = @n
then 'true'
else 'false or unknown'
end;
-- Alternative
select case
when
and
then 'true'
else 'false or unknown'
end;
go
--==============================================================
========
--** Replacing Multiple Contiguous Spaces With a Single Space
declare
@str as varchar(1000) = 'this is a string with
lots of spaces';
select replace(replace(replace(@str, ' ', '~ '), ' ~', ''), '~
', ' ');
go
--==============================================================
========
--** Replacing Overlapping Occurrences
declare @str as varchar(1000) = '.a.a.aba.a.a.aca.';
select replace(replace(@str, '.a.', '.p.'), '.a.', '.p.');
go
--==============================================================
========
--** String Formatting Numbers With Leading Zeros
declare
@num as int = -1234;
select case sign(@num) when -1 then '-' else '' end
+ replace(str(abs(@num), 10), ' ', '0');
-- SQL 2012
select format(@num, '0000000000');
go
--==============================================================
========
--** Left Trimming Leading Occurrences of a Character
--** Note : Only left zeros are replaced
declare
@str as varchar(100) = '0000001200900';
select replace(ltrim(replace(@str, '0', ' ')), ' ', '0');
go
--** If there are spaces in the string
declare
@str as varchar(100) = '0000001200900 xyz';
select replace(replace(ltrim(replace(replace(@str, ' ', '~'),
'0', ' ')), ' ', '0'), '~', ' ');
go
--==============================================================
========
--** Check String only for occurences of digits
declare
@str varchar(10) = '1234',
@str2 varchar(10) = '-1234'
select case
when @str not like '%[^0-9]%'
then 'true'
else 'false or unknown'
end,
case
when @str2 not like '%[^0-9]%'
then 'true'
else 'false or unknown'
end;
go
--** Use in table declaration CHECK (col1 NOT LIKE '%[^0-9]%')
--==============================================================
========
--** Checking a particular string can be converted to integer
declare
@str varchar(100) = '-1234.5678';
select cast(@str as int)
/*
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' -1234.5678
' to data type int.
*/
go
--** If not possible to convert, return NULL , no error message
declare
@str varchar(100) = '-1234.5678 ';
select case
when isnumeric(@str) = 1
and @str not like '%[^0-9!-+ ]%' escape '!'
and cast(@str as numeric(38, 0)) between -2147483648 and
2147483647
then cast(@str as int)
end;
set @str = '-1234';
select case
when isnumeric(@str) = 1
and @str not like '%[^0-9!-+ ]%' escape '!'
and cast(@str as numeric(38, 0)) between -2147483648 and
2147483647
then cast(@str as int)
end;
--** SQL 2012 and above
select try_convert(int, @str);
go
--==============================================================
========
--Validate for allowed characters in a sting
-- If the sting contains even one character which is not in the
list, returns 1 else 0
declare
@string varchar(20)
set @string ='abcd01234'
select case when @string like
'%[^a-zA-Z0-9!@~#$\^&*()\_+-;:",.{}]%' escape '\' then 1 else 0
end
--==============================================================
========
-- To check only alphacharacters in the sting ( a-z , A-Z)
declare @string varchar(20) s
et @string ='azAZ'
select case when @string like '%[^a-zA-Z]%' then 1 else 0 end
--==============================================================
========
- Sort numerically when numbers are in the string
create table #test(col varchar(10)not null primary key)
insert into #test values ('a104')
insert into #test values ('a152')
insert into #test values ('a1010')
insert into #test values ('a15')
insert into #test values ('a17')
insert into #test values ('aa130')
insert into #test values ('b11')
insert into #test values ('b30')
-- usual sort
select * from #test order by col
-- sort on numeric numbers
select * from #test
order by substring (col, 0,patindex('%[0-9]%',col))+
right('00000' + substring (col,patindex('%[0-9]%',col) ,
datalength(col)),5)
drop table #test
go
--==============================================================
========
--**checking for % when it is part of data
select c1
from
(
select 'universal%person' as c1 union all select
'universal'
) as t
where c1 like '%\%%' escape '\'
select c1
from
(
select 'universal%person' as c1 union all select 'universal'
) as t
where c1 like '%[%]%' escape '\'
--==============================================================
========
*****************************************************