Solved

charindex()

Posted on 2009-04-03
5
1,466 Views
Last Modified: 2013-11-10
Hi,
i am trying to find out first part of my filename is numeric or not using followed function
DECLARE  @filename varchar(100)
SET @filename = ?
SELECT     isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1
when i give the filename directly and check it works well in SSMS,but when i pass the file name through an input param in SSIS it fails..any help appriciated..
0
Comment
Question by:ametuer999
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
Is the entire thing throwing an error?  I'm not familiar with passing parameters to SSIS, but perhaps the ? has to be in quotes.  I thought that you could read the variable another way though.


DECLARE  @filename varchar(100)
SET @filename = '?'
SELECT     isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
For testing purposes add a "print @filename" statement to see what the variable is actually is.

Where is the question mark coming from anyway?
DECLARE  @filename varchar(100)

SET @filename = ?

print @filename

SELECT  isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
You could also check this using script rather than T-SQL. Then you don't need a database connection to check if your file is numeric.
0
 

Author Comment

by:ametuer999
Comment Utility
could you please give me a sample code...like....
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
Comment Utility
Below is some very simple code.
It inspects a variable called FileName. It grabs the characters before an underscore and checks if they are numeric.
It uses dialog boxes to help you debug.
To use it:
1. Drag a Script Task on to your page
2. Double click it. Press the Script selector on the left. Press the Design Script button
3. Paste in the code below
4. Ensure you have a variable called FileName, with a value in it
5. Right click on the script task and press Execute Task
You'll see some message boxes come up and finally tell you whether the chars preceding the _ are numeric or not.
If this suits you let me know and we can integrate it further. I assume this relates to your other question about looping through files.
PS I am not a .Net coding expert, there may be optimisations on this code.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.
 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime
 

Public Class ScriptMain
 

    Public Sub Main()

        Dim sVariable As String

        Dim iInteger As Integer

        Dim bResult As Boolean

        Dim vars As Variables
 

        Dts.VariableDispenser.LockOneForRead("FileName", vars)

        sVariable = vars(0).Value.ToString

        vars.Unlock()
 
 

        MsgBox("Value of variable is [" & sVariable & "]")
 

        If sVariable.IndexOf("_") > 0 Then

            sVariable = Left(sVariable, sVariable.IndexOf("_"))

        Else

            sVariable = ""

        End If
 

        MsgBox("Value of string preceding _ is [" & sVariable & "]")
 

        bResult = iInteger.TryParse(sVariable, iInteger)
 

        MsgBox("Starts with numeric? " & bResult.ToString)
 

        Dts.TaskResult = Dts.Results.Success

    End Sub
 
 

End Class

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now