Trim SQL field (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Joe Kauffman -> Trim SQL field (1/18/2005 11:37:05)

First off I want to thank Spooky for helping me figure out how to combine fields in SQL, now I need to be able to split a city, state field apart. I have a field in SQL that shows this format, "Denver, CO" or "Ouray, CO" and I need to be able to trim everything to the left of "CO" and display the results. I also need to be able to remove the last 4 characters for instance remove the ", CO" so I can just display the city.

Any thoughts?



ou812 -> RE: Trim SQL field (1/18/2005 13:09:47)

Here is a page that shows the string functions and how to manipulate what you need: http://www.learnasp.com/learn/strings.asp



Joe Kauffman -> RE: Trim SQL field (1/18/2005 13:20:31)

Thanks for the help Brian. But that shows how to return what I want in an ASP page. I'm trying to do this in SQL. My first thought was to do RIGHT(fieldname,2) to get the state information and then some sort of trim to get rid of the last 4 characters but the in the table, the field is specified as 40 length and I don't get anything when I do the Right(....



DesiMcK -> RE: Trim SQL field (1/18/2005 17:23:22)

Would this work?

<%=Left(fliedname,instr(fieldname,",")-1)%>

instr(fieldname,",") would give you the numeric position of the comma. So then if you take one away and use LEFT to trim the string.

Desi



Joe Kauffman -> RE: Trim SQL field (1/18/2005 17:28:25)

Desi,

Thanks for the input. That also looks more like ASP code than SQL. I'm trying to do this in a SQL view.



DesiMcK -> RE: Trim SQL field (1/18/2005 17:45:26)

How about this then:

<%
dim citytrim
citytrim = Left(fliedname,instr(fieldname,",")-1)
%>

mySQL = "SELECT * FROM MyTable WHERE City = " & citytrim

Desi



Joe Kauffman -> RE: Trim SQL field (1/18/2005 17:48:17)

Desi,

I tried the first example in an ASP page and it works for getting the city name out. How will I need to modify it to get the state info if the field contains "City ,St". Would I simply need to to a Right( instead of Left( ?



DesiMcK -> RE: Trim SQL field (1/18/2005 17:53:37)

No. I think you would need:

<%
dim statetrim
statetrim= Right(fieldname,(Len(fieldname)-instr(fieldname,","))
%>

Desi



Joe Kauffman -> RE: Trim SQL field (1/18/2005 17:56:38)

Desi,

I'm getting this:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

Is there a ")" missing?



Joe Kauffman -> RE: Trim SQL field (1/18/2005 17:58:31)

Nevermind, I got it. It was missing a ')' at the end.

I still need to figure out how to do this on my SQL database view.

Thanks a ton for the help Desi.




Joe Kauffman -> RE: Trim SQL field (1/20/2005 13:10:09)

Thanks for the help Desi and sorry for the confusion. This works well in the SQL view and stored procedure I created. The only thing that I'm not sure about is that I am trying to pass a start date and end date in my asp page and call the stored procedure to give me any records that are between @startdate and @enddate. If it is in varchar can I still use it to filter for the date range I want? My view looks like this: 

CREATE VIEW dbo.VW_APPTRPT
AS
SELECT     TOP 100 PERCENT dbo.HC1FLS_KCBUYR.KCBYLS AS BuyerLN, dbo.HC1FLS_KCBUYR.KCBYNM AS BuyerNM, 
                      dbo.HC1FLS_KCBUYR.KCBYMI AS BuyerMI, dbo.HC1FLS_KCBUYR.KCBYAD AS BuyerAD, dbo.HC1FLS_KCBUYR.KCBYCT AS BuyerCitySt, 
                      dbo.HC1FLS_KCBUYR.KCBYZC AS BuyerZip, dbo.HC1FLS_KCBUYR.KCBYHT AS BuyerHomePhone, 
                      dbo.HC1FLS_KCBUYR.KCBYWT AS BuyerWorkPhone, dbo.HC1FLS_KCBUYR.KCUNIT, dbo.HC1FLS_KCBUYR.KCJOB1, dbo.HC1FLS_KAMAST.KAPLAN, 
                      dbo.HC1FLS_KAMAST.KAELEV, dbo.HC1FLS_KCBUYR.KCSTAT, CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar) 
                      + '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar) 
                      + '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE, dbo.HC1FLS_KAMAST.KAMM21, dbo.HC1FLS_KAMAST.KADD21, 
                      dbo.HC1FLS_KAMAST.KACC21, dbo.HC1FLS_KAMAST.KAYY21
FROM         dbo.HC1FLS_KCBUYR INNER JOIN
                      dbo.HC1FLS_KAMAST ON dbo.HC1FLS_KCBUYR.KCJOB1 = dbo.HC1FLS_KAMAST.KAJOB1 AND 
                      dbo.HC1FLS_KCBUYR.KCUNIT = dbo.HC1FLS_KAMAST.KAUNIT
WHERE     (dbo.HC1FLS_KCBUYR.KCJOB1 > 100) AND (dbo.HC1FLS_KCBUYR.KCSTAT = 'SAL')
ORDER BY dbo.HC1FLS_KCBUYR.KCJOB1, dbo.HC1FLS_KCBUYR.KCUNIT


Thanks again for the help.



rdouglass -> RE: Trim SQL field (1/20/2005 15:50:58)

quote:

If it is in varchar can I still use it to filter for the date range I want?


Dates in varchar fields will not sort/compare properly as dates. They must be date/time fields or converted first.



Joe Kauffman -> RE: Trim SQL field (1/20/2005 15:52:50)

Thanks rdouglas. Then should I change this code somehow to convert it to datetime?

CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar)
+ '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar)
+ '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE



rdouglass -> RE: Trim SQL field (1/20/2005 15:57:59)

quote:

CAST(dbo.HC1FLS_KAMAST.KAMM21 AS varchar)
+ '/' + CAST(dbo.HC1FLS_KAMAST.KADD21 AS varchar) + '/' + CAST(dbo.HC1FLS_KAMAST.KACC21 AS varchar)
+ '0' + CAST(dbo.HC1FLS_KAMAST.KAYY21 AS varchar) AS FINALDATE


Have you tried something like this:

CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21)
+ '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS varchar) AS FINALDATE

Just a quick stab....hope it helps.



Joe Kauffman -> RE: Trim SQL field (1/20/2005 16:01:30)

That looks like almost the same thing as I have? I placed it in and ran it and it says Error converting data type varchar to numeric.



rdouglass -> RE: Trim SQL field (1/20/2005 16:07:19)

quote:

CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21)
+ '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS varchar) AS FINALDATE


OOOPS! Maybe this: (told you I didn't test it)

CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21)
+ '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE

Although you may have better luck with CONVERT:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp



Joe Kauffman -> RE: Trim SQL field (1/20/2005 16:14:03)

I tried it and it says Line1: Incorrect syntax near (/). I'm assuming it is the '/' between Date / Time.



rdouglass -> RE: Trim SQL field (1/20/2005 16:27:29)

Have you looked at the link I posted? Again, you may have to CONVERT and not CAST.



rdouglass -> RE: Trim SQL field (1/20/2005 16:28:35)

quote:

CAST(((dbo.HC1FLS_KAMAST.KAMM21) + '/' + (dbo.HC1FLS_KAMAST.KADD21) + '/' + (dbo.HC1FLS_KAMAST.KACC21)
+ '0' + (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE


Or maybe:

CAST(((dbo.HC1FLS_KAMAST.KAMM21) & '/' & (dbo.HC1FLS_KAMAST.KADD21) & '/' & (dbo.HC1FLS_KAMAST.KACC21)
& '0' & (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE



Joe Kauffman -> RE: Trim SQL field (1/20/2005 16:33:32)

rdouglas, thanks again for taking the time to help me. I tried the:

CAST(((dbo.HC1FLS_KAMAST.KAMM21) & '/' & (dbo.HC1FLS_KAMAST.KADD21) & '/' & (dbo.HC1FLS_KAMAST.KACC21)
& '0' & (dbo.HC1FLS_KAMAST.KAYY21)) AS date/time) AS FINALDATE

and it still gives me an error regarding the '/' between date/time. If I try taking this out it says "Invalid operator for data type. Operator equals boolean AND, type equals decimal."

I looked at the link you gave and the convert is somewhat new to me so I'll have to try to figure out what to do with it.

I've learned a ton from this forum. Thanks again to everyone.



Joe Kauffman -> RE: Trim SQL field (1/21/2005 11:31:34)

Man I'm thoroughly cornfused now. I've tried several convert statements and nothing seems to work! All I keep getting is error converting data type varchar to numeric. Any thoughts?



rdouglass -> RE: Trim SQL field (1/21/2005 11:40:40)

quote:

converting data type varchar to numeric


Why are you trying to covert to numeric? I thought you were doing a Date/Time comparison?



Joe Kauffman -> RE: Trim SQL field (1/21/2005 11:45:37)

I am that's what is so confusing. I've been working only with datetime.



rdouglass -> RE: Trim SQL field (1/21/2005 11:57:16)

OK. I built a test table in SQL Server and populated 3 varchar fields with numerical equivalents with month,day, and year numbers and ran this VIEW:

SELECT CAST(Text1 + '/' + Text2 + '/' + Text3 AS DateTime) AS Expr1 FROM dbo.tblTestingOF

and was returned valid dates. Sorry 'bout the earlier syntax issue.[:(] It does look like the CAST verb will work for you. There may be other issues but CAST should return a valid date to compare to. Does this get you any closer?



Joe Kauffman -> RE: Trim SQL field (1/21/2005 12:09:36)

Ok, I tried using this:

CAST(KAMM21 + '/' + KADD21 + '/' + KACC21 + '0' + KAYY21 AS DateTime) AS FINALDATE

and it still gives me the same varchar to numeric error. I don't know if it makes any difference but the fields are decimal type in the table I'm pulling them from. When I remove the '/' and '0' it will run but only gives me 1/01/1900.



rdouglass -> RE: Trim SQL field (1/21/2005 12:50:03)

quote:

fields are decimal type in the table


Oh.[8|] I was under the distinct impression that they were numerals only in a varchar field.

Can I ask why they're in decimal format? Is that a requirement somewhere else?

Maybe I don't understand the Q: I thought you were basically rebuilding the date from 3 fields; a month value, a day value, and a year value. Am I mistaken? And you want to do a date compare on the results correct?



Joe Kauffman -> RE: Trim SQL field (1/21/2005 13:09:49)

The fields are imported from a DTS package from our AS400. My initial intent was to be able to compare a start and end date that a user provides in the asp page and select any records that are between @startdate and @enddate. The fields coming in from the AS400 are the month (KAMM21), day (KADD21), century (KACC21) and year (KAYY21). I was hoping to combine these into one field to test against. Do you think there is a better way without having to combine them?

I appologize if I was vague or misleading. Hopefully this clears it up for you.



rdouglass -> RE: Trim SQL field (1/21/2005 13:21:08)

quote:

The fields coming in from the AS400 are the month (KAMM21), day (KADD21), century (KACC21) and year (KAYY21).


Can you give me 1 or 2 examples of what a typical dataset being returned might look like? You said decimal format, are these fields being returned as numbers (as opposed to characters)?



Joe Kauffman -> RE: Trim SQL field (1/21/2005 14:40:36)

Here's a couple of examples:

KAMM21 KADD21 KACC21 KAYY21
1 4 20 5
2 3 20 5
1 12 20 5




rdouglass -> RE: Trim SQL field (1/21/2005 14:52:54)

quote:

SELECT CAST(Text1 + '/' + Text2 + '/' + Text3 AS DateTime) AS Expr1 FROM dbo.tblTestingOF


So with my example from above and converting those fields to int, I used this:

SELECT CAST(CAST(Text1 AS VARCHAR) + '/' + CAST(Text2 AS VARCHAR) + '/' + CAST(Text3 AS VARCHAR) AS DateTime) AS Expr1
FROM dbo.tblTestingOF

to return Date values. SO with yours maybe something like:

CAST(CAST(KAMM21 AS VARCHAR) + '/' + CAST(KADD21 AS VARCHAR) + '/' + CAST(KACC21 AS VARCHAR) + '0' + CAST(KAYY21 AS VARCHAR) AS DateTime) AS FINALDATE

That confusing enough?



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625