How Can I achieve this

Question

Post reply

How Can I achieve this

Namshub

SSC Journeyman

Points: 79
More actions
September 3, 2003 at 6:45 am

#81097

I have a database that is not case sensitive (and i can't change it either)
I have the following script.
declare @teststr varchar(100)
--This string is the representation of the codes within the Onestaff
--finding the position in this string idicates the current associated code with that character
set @teststr ='ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$abcdefghijklmnopqrstuvwxyz%^&*()+=|.{[}]<>?/:"-~'';'
print @teststr
print CHARINDEX('k',@teststr)
When run i get 11 (ie "K") how can i return the correct position using collation within the stored procedure only.
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

You will need to change the collation

e.g.

declare @test-2 table (teststr varchar(100) collate Latin1_General_BIN)

insert into @test-2 values ('ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$abcdefghijklmnopqrstuvwxyz%^&*()+=|.{[}]<>?/:"-~'';')

select CHARINDEX('k',teststr) from @test-2

select CHARINDEX('K',teststr) from @test-2

Steven

mccork SSC-Insane Points: 22288 More actions · Answer 2

Namshub,

You can try this:

 

declare @teststr varchar(100) 

set @teststr ='ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$abcdefghijklmnopqrstuvwxyz%^&*()+=|.{[}]<>?/:"-~'';'

print @teststr

PRINT CHARINDEX(CONVERT(VARBINARY(100), 'k'), CONVERT(VARBINARY(100), @teststr))

-- To test it for all values in your string, try:

 

DECLARE @i INT, @char CHAR(1)

SET @i = 0

WHILE @i < LEN(@teststr)

BEGIN

    SET @i = @i + 1

    SET @char = SUBSTRING(@teststr, @i, 1)

    PRINT CHARINDEX(CONVERT(VARBINARY(100), @char), CONVERT(VARBINARY(100), @teststr))

END

Cheers,

- Mark

Cheers,
- Mark

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

Maybe you can go for the colation-solution with your select. (check BOL)

e.g.print CHARINDEX('k' COLLATE Latin1_General_CS_AI ,@teststr COLLATE Latin1_General_CS_AI )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

dave_is_here Old Hand Points: 312 More actions · Answer 4

In a similar solution to Mark's, you could use the "ASCII" function to search through the string for a particular character.

The following code snippet shows what I mean:

DECLARE @C INT

SET @C = 0

WHILE @C < LEN(@teststr)

BEGIN

SET @C = @C + 1

IF ASCII(SUBSTRING(@teststr, @C, 1)) = ASCII('k') PRINT @C

END

As the ASCII values for lower and upper case characters are distinct this will find only extact matches for the given character.

Dave Leathem.
It's just what we asked for but not what we want! (The Customer's Creed)

Namshub SSC Journeyman Points: 79 More actions · Answer 5

Many thanks to all for your help, i'm gaining a bit more T-SQL from your different ways of solving the problem

Cheers