Link to home
Get AccessLog in
Avatar of Bernard Thouin
Bernard ThouinFlag for Switzerland

asked on

Can't convert a simple string manipulation operation to a dynamic SQL version in a T-SQL stored procedure

I have to convert a select statement in a T-SQL stored procedure to dynamic SQL to be able to run that procedure in different DBs, and one line in it fails constantly, it's the following (before conversion):

Left(SecuritiesAccNo, charindex('-', SecuritiesAccNo) -1)

It's a simple statement, taking the content of the SecuritiesAccNo field (defined as nvarchar(18)) from a table, and isolating the characters left of a hyphen found in the field normally at the 7th or 8th character.

I just can't seem to find the version that, as a string, will be executed correctly as part of an exec (@sql) statement. I always get an error with all the various versions I tried.

Can somebody help me there, I'm despairing.

Avatar of lcohan
Flag of Canada image

It may be NULL's related and I suggest add a ISNULL or COLEASCE function on the column. Or simply try use PATINDEX instead of CHARINDEX as both CHARINDEX and PATINDEX are used to get starting position of a pattern. The functional difference is that the PATINDEX can use wild characters in the pattern being searched whereas CHARINDEX can't.
Avatar of Bernard Thouin



Thanks for your answer, but in the meantime, I found the right syntax, a bit by chance :) Sorry for the bother.
Avatar of Bernard Thouin
Bernard Thouin
Flag of Switzerland image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access