Feeds:
Posts
Comments

Posts Tagged ‘string’


-- The objective is to find the second occurence of space and insert a carriage return. 

-- First Method:
----------------

DECLARE @passed VARCHAR(max)
SET @passed = 'First Second Third Fourth'

-- Extract all text until the second occurence 
select substring(@passed, 1,
		charindex(' ', @passed, charindex(' ', @passed) + 1)-1
		) [Space Occured First]


union  all 
select CHAR(13) + CHAR(10)
union 

-- Extract all text after the second occurence 
select substring(@passed
			, charindex(' ', @passed, charindex(' ', @passed) + 1)
			, len(@passed)
		) [Space Occured Second]
		
		
-- Second Method:
--------------------

declare @tbl_Foo table 
(
	FiledToChange varchar(50)
)

insert into @tbl_Foo values -- inserting this way can only be from SQL server 2008 and above 
('a b c')
,('1 2 3 4')
,('Single')

select FiledToChange as [Before] from @tbl_Foo

update @tbl_Foo
	set FiledToChange = ISNULL(
		case when charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1 > 0 then -- Look for a second space 
			substring(FiledToChange, 1, -- This will get two words 
				charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)-1)
			+ 
			CHAR(13) + CHAR(10) -- add the carriage return and line feed 
			+ 
			substring(FiledToChange -- add the rest of the text 
				, charindex(' ', FiledToChange, charindex(' ', FiledToChange) + 1)
				, len(FiledToChange))
			end 
		,FiledToChange)
			
select FiledToChange as [After] from @tbl_Foo
-- Since SQL does not support displaying multiple lines, you will notice that instead there is an extra space. Applications supporting multiple lines can read this and conver it to multiple lines

Read Full Post »

--create function dbo.udf_ExtractNumbersToFloat (@String varchar(max))
--returns float
--begin
------ ==== Test =======
DECLARE @String VARCHAR(1000)

SET @String = 'I can count 1 and 2 and 3 even 4.5';

------ ==== Test =======
WHILE PATINDEX('%[^0-9]%.%', @String) & gt;0
	SET @String = REPLACE(@String, SUBSTRING(@String, PATINDEX('%[^0-9]%', @String), 1), '')

DECLARE @Float FLOAT

SET @Float = convert(FLOAT, @String)

PRINT @Float -- test
	--    return  @Float
	--end

Read Full Post »

DECLARE @passed VARCHAR(max)

SET @passed = 'FirstBit exec dbo.usp_Sites 4'

PRINT substring(@passed, 1, -- Change 1 to the vlaue you want the extraction to start from
		patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted
	)
PRINT substring(@passed, patindex('% exec %', @passed) + 1, len(@passed) - patindex('% exec %', @passed))

-- The function Left() and Right() are also used when you want the extraction to start from either
-- left most or right most side.
-- To extract certain characters from a string using charindex
DECLARE @find AS VARCHAR(max)

SET @find = 'dbo'

PRINT substring(@passed, charindex(@find, @passed), len(@find))
-- Patindex could also have been used above but ii used the format >> '% [this is where your txet goes]  %' )
---Difference in patindex() and charindex()
PRINT patindex('%' + @find + '%', @passed)
PRINT charindex(@find, @passed)


Read Full Post »

declare @passed varchar(max)

set @passed = 'FirstBit exec dbo.usp_Sites 4'


print substring(@passed, 1,  -- Change 1 to the vlaue you want the extraction to start from
		patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted
	)

print substring(@passed, 
		patindex('% exec %', @passed) + 1, 
		len(@passed) - patindex('% exec %', @passed)
	)

-- The function Left() and Right() are also used when you want the extraction to start from either 
-- left most or right most side. 

Read Full Post »