Solved

Strip Characters?  Left() ??

Posted on 2007-04-04
14
291 Views
Last Modified: 2013-12-24
How can I strip characters in SQL?

I have this code in my where statement

WHERE some_id IN (4|y,5|y,6|y)

I want to remove the |y or |n, basically its "y" for yes and "n" for no, but I need to remove that if possible.. so I am left with:

WHERE some_id IN (4,5,6).  It will always have that structure.  In some cases the id might be 256|n, 257|y so its not just 1 digit to the left of the pipe delimiter

Any help appreciated.

-ws
0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 2
14 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853302
where substring(some_id ,0,len(some_id )-1) in (3,45,256)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853319
... the above code will strip off the last two characters (either '|y' or '|n') of the some_id field (assuming it is a char or varchar field)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853350
... which reminds me, if the field is char or varchar then the IN clause should be too:

where substring(some_id,0,len(some_id )-1)  in  ('3', '45', '256')
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:Westside2004
ID: 18853627
HI,

The field is of type INT.  I still get an error with this

      where substring(someIntColumn ,0,len(someIntColumn )-1) in (3,4,5,255)

The error is:

Invalid column name 'y'.

-ws
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853655
Please clarify -- how can the field be of type int if it contains values like: 256|y ?
0
 
LVL 1

Author Comment

by:Westside2004
ID: 18853661
Hi,

The field does not contain these values, they are getting passed like this from a form, I need to strip them out.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853701
oh -- so it is the values in the IN clause that you need to strip -- not the values in the column?

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853748
kind of kludgy, but for a first pass this works:

  where some_id  in  ( substring('3|y',0,len('3|y')-1), substring('45|y',0,len('45|y')-1), substring('256|y',0,len('256|y')-1) )

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853763
I assume that the values will be passed in as parameters, in which case the syntax would be:

  where some_id  in  ( substring(@param1,0,len(@param1)-1), substring(@param2,0,len(@param2)-1), substring(@param3,0,len(@param3)-1) )

0
 
LVL 33

Expert Comment

by:knightEknight
ID: 18853778
A different approach would be to insert the passed in values into a separate temporary table and then work with the data that way.
0
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 75 total points
ID: 18853821
BTW - I originally viewed this question thru the SQL Server forum, which is why I am focusing on that type of solution.  It may be better to strip away the last two characters in CF before you put them in the SQL statement.
0
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 175 total points
ID: 18854014
While I'm a fan of letting the DB do most of the work, I think in this case you need to let CF parse them.

Something like this:
<cfset variables.myVar = "4|y,5|y,6|y">
<cfset variables.newVar = "">
<cfloop index="variables.i" from="1" to="#ListLen(variables.myVar)#">
 <cfset variables.value = ListGetAt(ListGetAt(variables.myVar,variables.i),1,'|')>
 <cfset variables.newVar = ListAppend(variables.newVar,variables.value)>
</cfloop>
<cfoutput>#variables.newVar#</cfoutput>

Replace variables.myVar with the form variable.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 18854084
Yes, that did the trick, the temp table was an idea I was thinking of too.  Thanks both of you for the help.  CF was the way to go here I think I just was not sure how to code that out.

-ws
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 18857627
Glad it helped!
0

Featured Post

Turn your laptop into a mobile console!

The CV211 Laptop USB Console Adapter provides a direct Laptop-to-Computer connection for fast and easy remote desktop access with no software to install.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question