SQL string functions

[Yevgeny Efter]

Hello! I am writing a stored procedure, and in my stored procedure, I need to work with a string. I found some functions in SQL: DATALENGTH, and SUBSTRING. But I can't find information about how to work with strings in SQL. What I need is to walk through a string, exemine every character and make the string smaller depending on the characters. First, how can I compare ntext strings? And, second, maybe there are more string functions in SQL? Thanks in advance!

Have a nice day!

[A Wong]

You probably want to take a look at nvarchar and varchar types. You can also take a look at functions that might help you on msdn. http://msdn2.microsoft.com/en-us/library/ms186939.aspx\[[^](http://msdn2.microsoft.com/en-us/library/ms186939.aspx "New Window")]">

[Mark J Miller]

If you have SQL BOL installed here's the documetation for all string functions: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/6940a83d-5374-4af3-bb27-5d89c8af83ac.htm Otherwise, here's the online help docs: http://msdn2.microsoft.com/en-us/library/ms181984.aspx[^]

Mark's blog: developMENTALmadness.blogspot.com

[Yevgeny Efter]

My problem now is getting the last character of a ntext variable. I do it this way, but it doesn't seem to work: SUBSTRING(@strvar, DATALENGTH(@strvar), 0)

Have a nice day!

[Mark J Miller]

DECLARE @strvar VARCHAR(50) SET @strvar = 'hello world' SELECT SUBSTRING(@strvar, LEN(@strvar), 1)

Mark's blog: developMENTALmadness.blogspot.com

[Yevgeny Efter]

Alright, now I try to compare it to a '\' using LIKE, and it never works. How can I check if this character is '\' or not?

Have a nice day!

[Mark J Miller]

DECLARE @strvar VARCHAR(50) SET @strvar = 'hello world' SELECT CASE SUBSTRING(@strvar, LEN(@strvar), 1) WHEN '/' THEN 1 ELSE 0 END

Mark's blog: developMENTALmadness.blogspot.com

[Yevgeny Efter]

Why do I need the SELECT? Won't WHILE(SUBSTRING(@strvar, LEN(@strvar), 1) NOT LIKE '\') work?

Have a nice day!

[Mark J Miller]

You don't need SELECT, I'm just using it like a Debug.Print to see the value returned. Why are you using LIKE? You aren't making any kind of pattern comparison. You are comparing the result with a constant expression. You should be doing it this way: SUBSTRING(@strvar, LEN(@strvar), 1) <> '\'

Mark's blog: developMENTALmadness.blogspot.com

[Yevgeny Efter]

Thank you!

Have a nice day!