CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3124690
user3124690

Reputation: 433

How to find the First Character starting in SQL Server 2008

DECLARE @input   NVARCHAR(MAX) = 'Create Procedure   Test'
DECLARE @SearhString NVARCHAR(MAX) = 'Procedure'

DECLARE @index    BIGINT = CHARINDEX(@keyword, @input)

1- I want to find the length of the next character starting after string Procedure

2- After that I want to check if the dbo string exists after that length to the specified location.

Suppose the first character Test starting at position 14 after string Procedure, so I have to use

DECLARE @String varchar(20) = SUBSTRING(@input, 21, 10)

Declare @StringIndex BIGINT = CHARINDEX('dbo.', @String)   

Declare @FirstCharacterIndex BIGINT(This will be the index of the first character)

If @String not contains string dbo then stuff MySchema before string Test.

Please suggest.

UPDATE

I have updated my question 22 is the result of @index + @FirstCharacterIndex + 9

@index = 8

@FirstCharacterIndex = 4(3 count for spaces and 4th is for first character)

9 = No. of characters of string Procedure

Upvotes: 1

Views: 1025

Answers (1)

cyan
cyan

Reputation: 747

try this sql below:

DECLARE @input   NVARCHAR(MAX) = 'Create Procedure   Test';
DECLARE @input2   NVARCHAR(MAX) = 'Create Procedure dbo Test';
DECLARE @SearchString NVARCHAR(MAX) = 'Procedure';

DECLARE @index    BIGINT = CHARINDEX(@SearchString, @input);


select @input, @SearchString as searchstring, @index as Index_SearchString , @index +len(@SearchString) as Last_CharacterOfSearchString
, charIndex('Test', @input,@index +len(@SearchString)) as test_Index
, charIndex('dbo', @input,@index +len(@SearchString)) as dbo_Index
, substring(@input, 0, @index +len(@SearchString)) as str1
, substring(@input, @index +len(@SearchString), len(@input)-@index +len(@SearchString)) as str2

Declare @dbo_Index int
Declare @str1 nvarchar(max), @str2 nvarchar(max)
set  @dbo_Index = charIndex('dbo', @input,@index +len(@SearchString))

--select @dbo_Index
if @dbo_Index = 0 
begin
  set @str1 = substring(@input, 0, @index +len(@SearchString))
  set @str2 = substring(@input, @index +len(@SearchString), len(@input)-@index +len(@SearchString))

  select @str1 + ' ' + 'MySchema' + @str2
end

--,charIndex('dbo',@input2, @index +len(@SearchString)) as dbo_Index

Updated:
objective: Get the "Under Fire" first character index ("U")

DECLARE @input2   NVARCHAR(MAX) = 'Create Procedure    Under Fire   Test';
DECLARE @SearchString NVARCHAR(MAX) = 'Procedure';


--,charIndex('dbo',@input2, @index +len(@SearchString)) as dbo_Index
select substring(@input2,17,25)
, CHARINDEX(@SearchString,@input2,0) + len(@SearchString) as LastIndexOf_Procedure
, CHARINDEX('Test',@input2,0)
,len(@input2)
,substring(@input2, CHARINDEX(@SearchString,@input2,0) + len(@SearchString), CHARINDEX('Test',@input2,0)- (CHARINDEX(@SearchString,@input2,0)+len(@SearchString)))



set @SearchString= Ltrim(rtrim(substring(@input2, CHARINDEX(@SearchString,@input2,0) + len(@SearchString), CHARINDEX('Test',@input2,0)- (CHARINDEX(@SearchString,@input2,0)+len(@SearchString)))))

select @SearchString
select 
@input2
,@SearchString as SearchString
,CHARINDEX(@SearchString,@input2,0) as FirstCharacterIndex

Upvotes: 3

Related Questions