Help: Converting to SQL-Case Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help: Converting to SQL-Case Statement

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-01-31 : 10:08:24

I know I need this in a case statement, but i'm having problems with the Mid, InStrRev, InStr functions and the logic that goes with this...


SELECT Table1.[ColumnA], IIf([ColumnA]="Sometext","Sometext",IIf(Not IsNull([ColumnB]),Mid([ColumnC],5,InStrRev([ColumnC],"^")-5),IIf(InStr([ColumnA],"*")<>0,Left([ColumnA],InStr([ColumnA],"*")-2),[ColumnA]))) AS Column_Name

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-31 : 10:35:51

What does it do?

--
Gail Shaw
SQL Server MVP

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-01-31 : 10:48:17

It's normalizing the data and trying to pull out a Name from the data.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-31 : 11:43:34

Here is a start -- I don't know those non-Standard string functions, but here is a start:
SELECT Table1.col_a,
CASE WHEN col_a = 'sometext'
THEN 'sometext'
ELSE CASE WHEN col_b IS NOT NULL
THEN SUBSTRING (col_c, 5, INSTRREV(col_c, '^')-5)
ELSE CASE WHEN CHARINDEX(col_a, '*') <> 0
THEN SUBSTRING(col_a, 1, CHARINDEX(col_a, '*')-2)
ELSE col_a END
END
END AS col_name
FROM Foobar;

Does INSTRREV(col_c, '^') mean
CHARINEX (col_c, REVERSE(col_c), '^')
or what?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-01-31 : 12:06:59

I'm having problems with the REVERSE.

No red lines or anywhere else with:


THEN SUBSTRING  (col_c, REVERSE(col_c), '^')

However, I get the error


Argument data type nvarchar is invalid for argument 2 of substring function.

...Column C is nvarchar....but where do i put the -5??

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-31 : 12:12:58

This is why we need a verbal explanation of what you're trying to accomplish. MS SQL Server and MS Access have some similarities, but not in n their string functions. The substring command wants to know what string, where to start and when to stop, e.g., substring('Jim',2,1) would return 'i'. The '^' doesn't make sense in your abouve statement, unless you're trying to fin where it exists in the string,
in which case CHARINDEX (col_c, REVERSE(col_c), '^') tells you how far from the end it is.

Jim

Jim

Everyday I learn something that somebody else already knew

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-01-31 : 13:23:58

'^' is the end of the string. I have a lot of garbage in the data i'm working with, however i can pull a name out of the string because the names will end with '^'. Ex. John^Doe^

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-01-31 : 14:40:06

Everything is working fine, except one line of code...

[Code]
CASE
WHEN ColumnA= 'Sometext'
THEN 'Sometext'
WHEN ColumnB IS NOT NULL
Then substring(ColumnC,CHARINDEX('*',REVERSE(ColumnC]))-5)
WHEN CHARINDEX('^', ColumnA) <> 0
THEN SUBSTRING(ColumnA, 2, CHARINDEX('^',ColumnA)-2)
else ColumnA
END

The line of code that isn't working is:



Then substring(ColumnC,CHARINDEX('*',REVERSE(ColumnC]))-5)

My error is:


Invalid length parameter passed to the LEFT or SUBSTRING function.
[/code]

I'm working on the logic to try to figure it out (if i can even understand it), but any input would help.  Thanks everyone for your input..I'd still be stuck at square one without you all!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources