Only 7 digits SQL Syntax - SQL Server Forums

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)

Only 7 digits SQL Syntax

Author

Topic

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 10:59:25

Hello SQLTeam,

I have a table with this following information:

DocId
1109590
1109591
1109592
1109593
1109594
1109595
1109596
1109597
11095000
11095001
11095002
11095003
11095004
11095005
11095006
11095007
11095008
11095009
11095000
11095000100010
11095000100011
11095000100012
11095000100013
11095000100014
11095000100015
11095000100016
11095000100017
11095000100018
7777785
7777786
7777787
7777788
7777789
7777790
7777791
7777792
7777771
7777772
7777773
7777774

I have a textbox search for DocId
I have a checkbox for only 7 digits
I have a checkbox for 8 digits
I have a checkbox for 14 digits
I have a checkbox for all digits

when I type this number 11095 in textbox, it will display numbers :

Results
1109590
1109591
1109592
1109593
1109594
1109595
1109596
1109597
11095000
11095001
11095002
11095003
11095004
11095005
11095006
11095007
11095008
11095009
11095000
11095000100010
11095000100011
11095000100012
11095000100013
11095000100014
11095000100015
11095000100016
11095000100017
11095000100018

With textbox search : 11095, If I checked box for only 7 digits I would like to display the results of 7 digits from the value search I specified in the textbox:

Results
1109590
1109591
1109592
1109593
1109594
1109595
1109596
1109597

With textbox search : 11095, If I checked box for only 8 digits I would like to display the results of 8 digits from the value search I specified in the textbox:

Results
11095000
11095001
11095002
11095003
11095004
11095005
11095006
11095007
11095008
11095009
11095000

With textbox search : 11095, If I checked box for only 14 digits I would like to display the results of 14 digits from the value search I specified in the textbox

Results
11095000100010
11095000100011
11095000100012
11095000100013
11095000100014
11095000100015
11095000100016
11095000100017
11095000100018

With textbox search : 11095, If I checked box for only All digits I would like to display the results of all digits from the value search I specified in the textbox

Results
1109590
1109591
1109592
1109593
1109594
1109595
1109596
1109597
11095000
11095001
11095002
11095003
11095004
11095005
11095006
11095007
11095008
11095009
11095000
11095000100010
11095000100011
11095000100012
11095000100013
11095000100014
11095000100015
11095000100016
11095000100017
11095000100018

What is the correct sql query syntax?

SELECT SUBSTR(Doc_ID, 7) From ABC WHERE DOC_ID = ‘ “ & textBoxSearch & “ ‘

SELECT CONVERT(varchar(7), doc_id(1,7) From ABC

maybe none above is correct.

I hope you could help me on this. Your help is very appreciated.

Thank you

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 11:54:01

what have you tried?

If you don't have the passion to help people, you have no passion

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 12:03:17

Thanks for reading my post.

I have been trying but unsuccefully.
See this line of Sql query:

SELECT doc_id , abbrev convert(varchar(2),datepart(mm,date)) + '/'
+ convert(varchar(2),datepart(dd,date)) + '/'
+ convert(varchar(4),datepart(yyyy,date)) ,
FROM ABC
WHERE doc_id not like '%[^0-9]%
ORDER BY cast(doc_id as integer)

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 12:06:22

sure. what is your query doing? do you understand what the query you posted is doing?

If you don't have the passion to help people, you have no passion

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 12:28:51

Yes, I do understand. After run that query I posted, is not correct. Gave me error : The conversion of the varchar value '9999999999' overflowed an int column. Maximum integer value exceeded.

I hope you could help me to find the right SQL Query that display only 7 digits when checked box... so on...

See my VB.net programming

Dim rx As Regex
Dim m As Match = Match.Empty
Dim CharFlag As Boolean

'check for valid entries that conform to regular expressions:
If chk7digits.Checked Then

rx = New Regex("^\d{7}$")
CharFlag = True 'always set to pass
Else
CharFlag = False
End If

'if the entry is in valid form:
If m.Success And CharFlag = True Then
'initialize sorting:
Session("SortExpression") = String.Empty
Session("SortOrder") = String.Empty

'get the data and load the grid:
DataFill()

Else 'if the entry is not valid:

ShowAlert("Not 7 digits found.")
end if

Datafill()

SQL Query

SELECT doc_id,
convert(varchar(2),datepart(mm,date)) + '/'
+ convert(varchar(2),datepart(dd,date)) + '/'
+ convert(varchar(4),datepart(yyyy,date)) ,
FROM ABC
WHERE doc_id LIKE '" & txtEntryNumber.Text & "%' ORDER BY cast(doc_id as integer)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 13:08:06

I would be happy to help you. but even better I would be happy to help you understand tsql programming. so are you working with SSIS or TSQL? what is up with the vb.net code?
every time you are posting you put a different sql query which one are you currently using?

If you don't have the passion to help people, you have no passion

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 13:40:35

Yosiasz,

To be honest I understand very well the TSQL Programming. The reason of VB.Net code is because I am Visual Basic.Net programmer, and I am doing a Data search project with SQL Database with millions and millions of data. I am sorry the SQL queries I post because I was testing... I really appreciate if you could help me. Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 14:08:21

please try this


declare @docs table(docid bigint)
declare @parser int
declare @DocPrefix nvarchar(255)

insert into @docs
SELECT 1109590
UNION
SELECT 1109591
UNION
SELECT 1109592
UNION
SELECT 1109593
UNION
SELECT 1109594
UNION
SELECT 1109595
UNION
SELECT 1109596
UNION
SELECT 1109597
UNION
SELECT 11095000
UNION
SELECT 11095001
UNION
SELECT 11095002
UNION
SELECT 11095003
UNION
SELECT 11095004
UNION
SELECT 11095005
UNION
SELECT 11095006
UNION
SELECT 11095007
UNION
SELECT 11095008
UNION
SELECT 11095009
UNION
SELECT 11095000
UNION
SELECT 11095000100010
UNION
SELECT 11095000100011
UNION
SELECT 11095000100012
UNION
SELECT 11095000100013
UNION
SELECT 11095000100014
UNION
SELECT 11095000100015
UNION
SELECT 11095000100016
UNION
SELECT 11095000100017
UNION
SELECT 11095000100018
UNION
SELECT 7777785
UNION
SELECT 7777786
UNION
SELECT 7777787
UNION
SELECT  7777788
UNION
SELECT 7777789
UNION
SELECT 7777790
UNION
SELECT 7777791
UNION
SELECT 7777792
UNION
SELECT 7777771
UNION
SELECT 7777772
UNION
SELECT 7777773
UNION
SELECT 7777774

SET @DocPrefix = '11095'
SET @parser =  7 

SELECT docid 
  From @Docs
 WHERE LEN(docid) =  @parser
    AND LEFT(CAST(docid as nvarchar(max)),LEN(@DocPrefix)) LIKE @DocPrefix

If you don't have the passion to help people, you have no passion

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 14:35:18

I tried and test your SQL Query. It was very interesting, nice and it worked so well.

Since I have this following SQL Query, please see it:

SELECT doc_ID [Document] FROM ABC
WHERE doc_ID LIKE '11095%'
ORDER BY cast(doc_ID as integer)

What should I put on my sql query from your SQL Query

Should I put this way:

SELECT doc_ID [Document] FROM ABC
WHERE doc_ID LIKE '11095%' AND LEN(doc_ID) LIKE 7 AND LEFT(CAST(doc_ID as integer(max))
ORDER BY cast(doc_ID as integer)

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-08 : 15:50:37

you want to make it dynamic right? so you cannot put LIKE '11095%'
You need to create a sproc and pass it the two variables @parser, @DocPrefix
Since you understand very well the TSQL Programming that should be a breeze


SELECT doc_ID [Document] FROM ABC 
 WHERE LEN(docid) =  @parser
    AND LEFT(CAST(docid as nvarchar(max)),LEN(@DocPrefix)) LIKE @DocPrefix

If you don't have the passion to help people, you have no passion

2011SQL
Starting Member

12 Posts

Posted - 2011-06-08 : 17:54:11

I got it. Thank you so much for your attention and help.
Thanks again, yosiasz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources